CODEWITHSUNDEEP
mysqlsqlalchemyflask-sqlalchemy
mrquintopolous
mrquintopolous

Reputation: 157

Join between sub-queries in SQLAlchemy

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.

Upvotes: 0

Views: 3834

Answers (1)

van
van

Reputation: 76972

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)
     )

Upvotes: 1

Related Questions