Geek asks and answers

MySQL and SQLAlchemy: Getting N Recent Comments for Multiple Items

I am trying to write a query to get N last comments for several items.

I am currently looking at elements with an element request:

for i in itemIds:
    Comment.query.filter_by(itemId=i).order_by(Comment.id.desc()).limit(3)

But it is very slow.

I would like to have one request that receives all the comments but does not know how to do this. I tried to use union, but did not get it to work. I see that there are problems with MySQL, order_byand union. I'm trying to do something:

a = Comment.query.filter_by(itemId=1).order_by(Comment.id.desc()).limit(3)
b = Comment.query.filter_by(itemId=2).order_by(Comment.id.desc()).limit(3)
u = union_all(a,b)
DB.session.query(Comment).select_from(u).all()

But that does not work. He complains about "Misuse of UNION and ORDER BY."

I am not a MySQL or SQLAlchemy ninja, and have been banging my head about it for hours.

Help me please! Any pointers or tips would be greatly appreciated.

+5
1

SQL . " N " http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

SQLAlchemy:

c2 = aliased(Comment)
query = session.query(Comment).filter(
            session.query(func.count(c2.id))\
                .filter(c2.id >= Comment.id)\
                .filter(c2.item_id == Comment.item_id)\
                .order_by(c2.id.desc())\
                .correlate(Comment)\
                .as_scalar() <= 3)\
    .filter(Comment.item_id.in_(itemIds)).all()
+5

More articles:


All Articles