Join between sub-queries in SQLAlchemy

Question

In relation to the answer I accepted for this post, SQL Group By and Limit issue, I need to figure out how to create that query using SQLAlchemy. For reference, the query I need to run is:

SELECT t.id, t.creation_time, c.id, c.creation_time
FROM (SELECT id, creation_time  
      FROM thread
      ORDER BY creation_time DESC
      LIMIT 5
     ) t
LEFT OUTER JOIN comment c ON c.thread_id = t.id
WHERE 3 >= (SELECT COUNT(1) 
            FROM comment c2 
            WHERE c.thread_id = c2.thread_id 
            AND c.creation_time <= c2.creation_time
           )

I have the first half of the query, but I am struggling with the syntax for the WHERE clause and how to combine it with the JOIN. Any one have any suggestions?

Thanks!

EDIT: First attempt seems to mess up around the .filter() call:

c = aliased(Comment)
c2 = aliased(Comment)

subq = db.session.query(Thread.id).filter_by(topic_id=122098).order_by(Thread.creation_time.desc()).limit(2).offset(2).subquery('t')

subq2 = db.session.query(func.count(1).label("count")).filter(c.id==c2.id).subquery('z')

q = db.session.query(subq.c.id, c.id).outerjoin(c, c.thread_id==subq.c.id).filter(3 >= subq2.c.count)

this generates the following SQL:

SELECT t.id AS t_id, comment_1.id AS comment_1_id 
FROM (SELECT count(1) AS count 
FROM comment AS comment_1, comment AS comment_2 
WHERE comment_1.id = comment_2.id) AS z, (SELECT thread.id AS id 
FROM thread 
WHERE thread.topic_id = :topic_id ORDER BY thread.creation_time DESC
 LIMIT 2 OFFSET 2) AS t LEFT OUTER JOIN comment AS comment_1 ON comment_1.thread_id = t.id 
WHERE z.count <= 3

Notice the sub-query ordering is incorrect, and subq2 somehow is selecting from comment twice. Manually fixing that gives the right results, I am just unsure of how to get SQLAlchemy to get it right.

van · Accepted Answer · 2015-01-16 20:44:48Z

1

Try this:

c = db.aliased(Comment, name='c')
c2 = db.aliased(Comment, name='c2')

sq = (db.session
      .query(Thread.id, Thread.creation_time)
      .order_by(Thread.creation_time.desc())
      .limit(5)
      ).subquery(name='t')

sq2 = (
    db.session.query(db.func.count(1))
    .select_from(c2)
    .filter(c.thread_id == c2.thread_id)
    .filter(c.creation_time <= c2.creation_time)
    .correlate(c)
    .as_scalar()
)

q = (db.session
     .query(
         sq.c.id, sq.c.creation_time,
         c.id, c.creation_time,
     )
     .outerjoin(c, c.thread_id == sq.c.id)
     .filter(3 >= sq2)
     )

answered Jan 16, 2015 at 20:44

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mrquintopolous Over a year ago

Thank you van, that does seem to work. Playing around with it, I am able to get the query to return a Comment object; is there a way to get the above query to return actual Model objects, or is that not possible due to the subquery?

van Over a year ago

It is possible, of course. In the final query (q) you have to query for an object you want, and not for a list of columns. In your case you could query for Thread and join it on the sq to have the same id column value.

Collectives™ on Stack Overflow

Join between sub-queries in SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related