0

I want to order the query result by the number of likes a post has received. This is my Table:

class Card(Base):
   __tablename__ = 'cards'
   id = Column(Integer, primary_key=True)
   post_id = Column(Integer)
   voted = Column(Boolean)

@hybrid_property
def likes(self):
   likes = session.query(Card).filter(and_(Card.post_id == self.post_id, Card.voted == True)).count()
   return likes

@likes.expression
def likes(cls):
   return (select([func.count(Card.id)]).where(and_(Card.post_id == cls.post_id, Card.voted == True))

This is the query:

cards = session.query(Card).filter(Card.user_id == current_user.get_id()).order_by(desc(Card.likes)).all()

The order_by part in SQL, which seems quite right to me:

ORDER BY (SELECT count(cards.id) AS count_1 FROM cards WHERE cards.post_id = cards.post_id AND cards.voted = true) DESC

But when I'm calling this after the query:

for card in cards:
    print card.likes

The output is not in descending but completely random order. My only guess now is that because of the filter, the count is only executed at those cards with card.user_id == current_user.id. If so (?), how do I order my query result by the total number of likes a post has received?

Things I looked up before: Hybrid Attributes 1, SQLAlchemy order by hybrid property 2,

Thank you.

Community
  • 1
  • 1
Ilija
  • 21
  • 7
  • That SQL is definitely not right. `cards.post_id = cards.post_id` is always true, so every row has `count_1` equal to the number of total cards with `voted = true` in the table, meaning the sort will just give you rows in no particular order. Can you elaborate on how `Card`, `Post`, and "likes" tie together? Otherwise we can't really suggest a solution. – univerio May 23 '16 at 23:37
  • Are you sure that you want to query `Card` and not the `Post` instances (ordered by number of votes their cards received)? – van May 25 '16 at 06:21

1 Answers1

-3

To be honest I don't see how your sql statement even works, below is the reference to postgresql order by.

http://www.postgresql.org/docs/9.4/static/queries-order.html http://www.tutorialspoint.com/postgresql/postgresql_having_clause.htm

And I believe your query should be rewritten like so:

SQL:

SELECT count(cards.id) AS count_1, cards.id
FROM cards
GROUP BY count_1
HAVING cards.voted = true
ORDER BY count_1 DESC

But even then it looks wrong to me, can we see your ERD?

Abraham
  • 230
  • 3
  • 15