On Mon, Sep 24, 2018 at 12:22 PM YKdvd <davidobe...@gmail.com> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
