> On Dec 4, 2014, at 7:33 PM, Seth <[email protected]> wrote: > > 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.
you’d take the example at http://stackoverflow.com/questions/9116924/how-can-i-achieve-a-self-referencing-many-to-many-relationship-on-the-sqlalchemy/9119764#9119764 <http://stackoverflow.com/questions/9116924/how-can-i-achieve-a-self-referencing-many-to-many-relationship-on-the-sqlalchemy/9119764#9119764>, and everywhere that deals with “secondary” you’d instead use the association object pattern: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object <http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object>. With the association table fully mapped, it would be much simpler to work with if you just queried for directly FriendAssociation objects. > > 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] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at http://groups.google.com/group/sqlalchemy > <http://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. -- 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.
