Build Model.query Of A Related Model

I need to build a query that lists all the users, there best friend and there total number of friends. The list has to be ordered by totalFriends a user has. I want the resulting

Solution 1:

I added relationship to your model as below:

class users(db.Model):
    __tablename__ = "Users"
    id = db.Column(db.Integer, primary_key=True)
    userName = db.Column(db.String, nullable=False)

    friends = association_proxy(
        '_friends', 'friend',
        creator=lambda v: friendships(friend=v),
    )

    best_friend = db.relationship(
        'users',
        secondary='BestFriends',
        primaryjoin='users.id==bestFriends.user_id',
        secondaryjoin='users.id==bestFriends.best_friend_id',
        uselist=False,
        backref=db.backref('best_friend_of', uselist=False),
    )


class friendships(db.Model):
    __tablename__ = "Friendships"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
    friend_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)

    user = db.relationship(users, foreign_keys=user_id, backref='_friends')
    friend = db.relationship(users, foreign_keys=friend_id)


class bestFriends(db.Model):
    __tablename__ = "BestFriends"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
    best_friend_id = db.Column(db.Integer, db.ForeignKey('Users.id'))

Added some test data:

# add data
u1, u2, u3, u4 = _users = [
    users(userName=_un)
    for _un in ('Alex', 'Carlos', 'Sara', 'Jack')
]
u1.friends.append(u2)
u1.friends.append(u3)
u2.friends.append(u4)
u3.friends.append(u1)

u1.best_friend = u2
u2.best_friend = u1
db.session.add_all(_users)
db.session.commit()

After that getting the query is pretty easy:

# create query
user_bf = db.aliased(users, name='user_bf')
userList = (
    users.query
    .add_column(db.func.count(friendships.user_id).label("total"))
    .add_column(user_bf.id.label("best_friend"))
    .add_column(user_bf.userName.label("best_friend_name"))
    .outerjoin(friendships, users.id == friendships.user_id)
    .outerjoin(user_bf, users.best_friend)
    .group_by(users.id)
    .order_by(db.func.count(friendships.user_id).desc())
    .paginate(1, 10, False)
)
foruserin userList.items:
    print(user)

But I would probably remove the table for best friend and add it directly to the users table.