On Oct 15, 2013, at 4:38 PM, Lukasz Szybalski <[email protected]> wrote:
> Hello,
>
> I have this query, but the the "IL.type" is incorrect. It looks like the
> first record value is returned instead of last:
>
> session2.query(func.max(IL._Sid),IL.Number,IL.DriverLicenseNumber,IL.Type,IL.ExpDate).filter(IL.Confirmed=='Yes').group_by(IL.Number,IL.DriverLicenseNumber,IL.ExpDate).all()
>
> Which by searching I should do:
>
> select xyz from query (above)?
> I could group by on IL.Type but then I would not get the latest record for
> that DL#,exp_date,Number.
>
> How would that look in sqlalchemy?
> aka something along:
> SELECT IL2.Number,IL2.DriverLicenseNumber,IL2.Type,IL2.ExpDate
> FROM IL2
> JOIN
> (session2.query(func.max(IL._Sid),IL.Number,IL.DriverLicenseNumber,IL.Type,IL.ExpDate).filter(IL.Confirmed=='Yes').group_by(IL.Number,IL.DriverLicenseNumber,IL.ExpDate)
>
> ) AS IL
> ON IL._Sid = IL2=_Sid
I'm having trouble parsing this because it's not clear what the tables are - is
IL2 a table? I see that "IL" is an alias name. I guess you mean for IL2 to
be an alias also?
to join to a subquery, you call subquery() on it:
q1 = sess.query(func.max(IL._Sid).label("_Sid"), IL...).filter(..).group_by(..)
subq = q1.subquery()
IL2 = aliased(IL)
q2 = sess.query(IL2.Number, IL2..., ).join(subq, IL2._Sid == subq.c._Sid)
this general form is illustrated at:
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-subqueries
signature.asc
Description: Message signed with OpenPGP using GPGMail
