0

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?

Abdul Hamid
  • 168
  • 1
  • 7
  • 25
  • As you asked for sqlalchemy, its worth looking https://stackoverflow.com/questions/5973553/sqlalchemy-ordering-by-count-on-a-many-to-many-relationship which is a great solution – Fernando Aug 06 '17 at 20:18

1 Answers1

0
select users.*, count(*) over (partition by friendship.user) 
from users inner join friendship on users.id = friendship.user
order by count
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Thanks man althought is olved it like this: SELECT m.*, SUM(case when f.monkey = m.id then 1 else 0 end) as friends\ FROM monkey as m\ LEFT JOIN friendship as f ON m.id = f.monkey\ GROUP BY m.id, m.name\ order by friends desc – Abdul Hamid Jun 19 '15 at 05:45