From raw sql to flask-sqlalchemy

Question

hi trying to execute this raw query with sqlalchemy:

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 

so far I get the result that I want with this raw request, but I want to be able to .paginate them so that I can work fine

with my other questions, what I did was the following:

 monkeys = models.Monkey.query.order_by(models.Monkey.name).paginate(page, 5, False) 

pretty simple and I got what I wanted, I believe that I need to do something like

 monkeys = models.Monkey.query.join(models.Friendship, db.func.count(models.Monkey.id == models.Friendship.monkey)) 

but I don’t get what I want, I know that I lost the sum () part, but I tried with func.c.count (), but I just don’t know how to make it work, is it possible to achieve in sqlalchemy? im using postgres btw

Answer 1

It looks like this will accomplish what you need

 monkeys = models.Monkey.query.outerjoin(models.Friendship, db.func.count(models.Monkey.id == models.Friendship.monkey)) #or if you really want to stick to your above query monkeys = models.Monkey.query.outerjoin(models.Friendship, db.func.SUM(func.IF(models.Monkey.id == models.Friendship.monkey, 1, 0))) 

FYI I'm used to doing this in mysql, func. * you call me a little different