CODEWITHSUNDEEP
pythonsqlalchemyflask
Osku
Osku

Reputation: 13

Ordering by relationship name in sqlalchemy

I have this kind of model for User. Users are able to have multiple friends and also one best friend.

friendship = Table('friendship', Base.metadata,
    Column('m1_id', Integer, ForeignKey('user.id')),
    Column('m2_id', Integer, ForeignKey('user.id'))
)
class User(Base):
    __tablename__ = 'user'
        id = Column(Integer, primary_key=True)
        name = Column(String(80), unique=True)
        email = Column(String(120), unique=True, info={'validators': Email()})
        age = Column(Integer())
        best_friend_id = Column(Integer, ForeignKey('user.id'))
        best_friend = relationship(lambda: User, remote_side=[id])
        friends = relationship('User',
            secondary=friendship,
            primaryjoin=id==friendship.c.m1_id,
            secondaryjoin=id==friendship.c.m2_id,
            backref=backref('ofriends', lazy='dynamic'),
            lazy='dynamic'
        )

I m trying to get a query witch contains count of friends and is ordered by the name of persons best friend, but i cannot figure out how.

query = db_session.query(User, func.count(friendship)-1).join(friendship, User.id==friendship.c.m1_id).group_by(User)

Then this fails:

query.order_by(User.best_friend.name).all()

Thank you

Upvotes: 1

Views: 229

Answers (1)

SingleNegationElimination
SingleNegationElimination

Reputation: 156138

friendship and best_friend are, in your model, unrelated. If you want to use the best_friend relationship in your query, you'll have to join it in somehow; since this is a self join, you also need to come up with an alias for the left and right instances of User, so they can be distinguished.

The following query gives users and the total number of friends they have, ordered by the name of whoever their best friend is (data about that best friend is not included in the result set)

>>> best_friend_alias = sa.orm.aliased(User)
>>> query = (Query([User, sa.func.count(friendship)-1])
...          .join(friendship, User.id==friendship.c.m1_id)
...          .join((best_friend_alias, User.best_friend))
...          .group_by(User)
...          .order_by(best_friend_alias.name))
>>> print query
SELECT "user".id AS user_id, "user".name AS user_name, "user".email AS user_email, "user".age AS user_age, "user".best_friend_id AS user_best_friend_id, count() - :count_1 AS anon_1 
FROM "user" JOIN friendship ON "user".id = friendship.m1_id JOIN "user" AS user_1 ON user_1.id = "user".best_friend_id GROUP BY "user".id, "user".name, "user".email, "user".age, "user".best_friend_id ORDER BY user_1.name

Upvotes: 1

Related Questions