On Mar 12, 2014, at 2:07 PM, Michael Weylandt <michael.weyla...@gmail.com> wrote:
> Hi Mike, > > I've simplified the query I'm looking for into a one line: (not sure why I > didn't think of HAVING earlier -- definitely making a mountain from molehills) > > SELECT A.*, B1.*, C1.* FROM A LEFT OUTER JOIN (SELECT * FROM B GROUP BY A_id > HAVING number = max(number)) AS B1 ON A.id = B1.A_id LEFT OUTER JOIN (SELECT > * FROM C GROUP BY A_id HAVING number = max(number)) AS C1 on A.id = C1.A_id; > > id letter id A_id word number id > A_id word number > ---------- ---------- ---------- ---------- ---------- ---------- > ---------- ---------- ---------- ---------- > 1 A 2 1 Bar 2 1 > 1 cat 5 > 2 B 2 > 2 dog 6 > 3 C 3 3 Baz 2 > > > I think this looks like the following in sqlalchemy: > > b_subq = session.query(B).group_by(B.A_id).having(B.number == > func.max(B.number)).subquery() > c_subq = session.query(C).group_by(B.A_id).having(C.number == > func.max(C.number)).subquery() > > q = session.query(A, bsubq, csubq).outerjoin(bsubq, bsubq.c.A_id == > A.id).outerjoin(csubq, csubq.c.A_id == A.id) > > print q > > But this doesn't seem to give the right results (omitting the "dog" + 6 > match). If I take the generated SQL and feed it "by hand" it works though, so > I'll have to look elsewhere to resolve that. > > I do note that only "A" is returned as an object. Is there any way to get "B" > and "C" wrapped up nicely by the ORM as well? as I mentioned earlier, you need to use aliased(B, bsubq) for that. as far as "the right results", turn on echo='debug' which will show the SQL being emitted as well as the results. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
