Hello, I'm trying to build a "friendship" self referential User->User relationship query using a join table, but I'd like to also be able to have access a column/attribute on the join table in the query result.
For reference, my setup is very similar to the one seen at http://stackoverflow.com/a/9119764/26278 (my join table is named "users_friends"), but with an extra "status" column in the join table. I'd like to use the UNION approach in the "User.all_friends" relationship seen there, but I'd also like the query to return the value of the "users_friends.status" column so that I can read the status of the relationship (e.g. pending, accepted, etc) in the result. I think what I'm looking for is a query similar to: SELECT users.*, anon_1.friend_status FROM users, ( SELECT users_friends.user_id AS user_id, users_friends.friend_user_id AS friend_user_id, users_friends.status AS friend_status FROM users_friends UNION SELECT users_friends.friend_user_id AS friend_user_id, users_friends.user_id AS user_id, users_friends.status AS friend_status FROM users_friends ) AS anon_1 WHERE anon_1.user_id = ? AND users.id = anon_1.friend_user_id However, I am at a loss as to how to implement this properly. Thanks in advance, Seth -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
