flask-sqlalchemy, sql count records in a relationship and then join to a users table

Question

Im querying my users this way with sql:

select * 
from users

i get all the users but now i want to query how many friends they have so if i do this

i do this:

select count(*) 
from users inner join friendship on users.id = friendship.user

and i get exactly how many friends they have but now i want to add this record to the users table in order to order them by this number

is there a way to achieve this with sqlalchemy?

Answer 1

select users.*, count(*) over (partition by friendship.user) 
from users inner join friendship on users.id = friendship.user
order by count