I think that got me to where I have a working hybrid. It looks something
like this (I broke it up into multiple statements for debugging):
@isLast.expression
def isLast(cls):
dmsq = aliased(dm, name="dmsq")
q = cls.id == select([dmsq.id]).where(dmsq.department_id ==
cls.department_id).order_by(dmsq.seqOrder.desc()).limit(1).as_scalar()
print q
return q
The subquery is really just sort of a lookup, and leaving out the
correlate() doesn't seem to chnge the query. But since "cls" refers to the
same object model ("dm"), I did have to use an alias ("dmsq") of it for the
subquery. I'm using MySQL, so the LIMIT() works. This seems to be doing
the right thing with my small test case, and the printed SQL is right, so
if it works with the full set of data and queries I'll declare victory.
Thanks again for the help.
On Tuesday, September 25, 2018 at 9:09:24 PM UTC-3, Mike Bayer wrote:
>
> On Mon, Sep 24, 2018 at 12:22 PM YKdvd <[email protected] <javascript:>>
> wrote:
> >
> > I have an ORM setup with a "departments_milestones" table ("dm",
> DepartmentMilestone objects), with "department_id" and "seqOrder" Fields.
> Each department has a few records in this table, ordered within the
> department by "seqOrder" (so seqOrder is not unique).
> >
> > Another object "ShotsStatus" contains a collection of these
> DepartmentMilestone objects, one for each department. I'm interested in
> getting the ShotsStatus records that are at the "last" milestone in its
> department - the one with the highest "seqOrder" value for the department.
> In SQL I can do something like this, with a subquery to select the
> departmental milestone records, reverse sort them so the 'last' one is
> first, and LIMIT 1 to get it, which is compared to the outer record.
> >
> > select * FROM shots_status ss
> > left outer join otherschema.departments_milestones dm on
> (dm.department_id=ss.dept_id AND dm.milestone_id = ss.status_id)
> > where ss.dept_id=723
> > and dm.id = (SELECT dmsq.id FROM plumber.departments_milestones dmsq
> WHERE dmsq.department_id=dm.department_id ORDER BY dmsq.seqOrder DESC LIMIT
> 1)
> >
> > I'd like to create a hybrid property "isLast" on the DepartmentMilestone
> object that returns True if it represents the 'last' milestone. I'm
> having trouble figuring out what sort of sqlalchemy select coding I'd need
> to recreate the subquery from the SQL code to do this.
>
>
> it's a correlated select so it's along the lines of (note this is not
> the exact SELECT you have):
>
> @myproperty.expression
> def myproperty(cls):
> return select([Dmsq.id]).where(Dmsq.id ==
> cls.dept_id).correlate(cls).order_by(Dmsg.seq).limit(1).as_scalar()
>
> the LIMIT 1 thing won't work on every backend but should make it on at
> least MySQL and Postgresql.
>
>
>
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
