[sqlalchemy] Hybrid property with subquery
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.
--
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.
Re: [sqlalchemy] Hybrid property with subquery
On Mon, Sep 24, 2018 at 12:22 PM YKdvd 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.
Re: [sqlalchemy] Hybrid property with subquery
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 >
> 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+...@googlegroups.com .
> > To post to this group, send email to sqlal...@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.
