But we're not looking for one row in the Certificate table, what it does is, for _every_ CSR, take the Certificate metadata that matches the _oldest_ certificate.
The equivalent using a Join would be the following (the below is the generated code): SELECT certificate.csr_id AS certificate_csr_id, certificate.not_before AS certificate_not_before, certificate.not_after AS certificate_not_after FROM certificate JOIN (SELECT certificate.csr_id AS csr_id, max(certificate.not_after) AS not_after FROM certificate GROUP BY certificate.csr_id) AS latest ON certificate.csr_id = latest.csr_id AND certificate.not_after = latest.not_after And while I'm pretty certain the above query isn't in any way the optimal one, I'm still a bit out of my depth on the SQL side of things. On Sun, Jul 26, 2015 at 8:33 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > > On 7/26/15 12:56 PM, Dennis Ljungmark wrote: > > Hello, all. > > I am trying to reshape the (somewhat tricky) SQL query below into > something SQLAlchemy can generate, and I just hit a wall: > > This is the query: > SELECT (c).csr_id, (c).not_before, (c).not_after FROM (SELECT (SELECT c FROM > certificate c WHERE c.csr_id=csr.id ORDER BY c.not_after DESC LIMIT 1) AS c > FROM csr offset 0) s; > > And, I just can't seem to get SQLAlchemy to emit a correlated scalar > subquery like this. > > correlated scalar subqueries are illustrated in the Core tutorial at > http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#scalar-selects. > I'm not sure why inklesspen's example isn't making use of as_scalar() or > stmt.label(), which would be required. Query supports the same use if > you are working with ORM queries. > > However, the statement "SELECT c FROM certificate c" and the "(c).csr_id" > syntax appears to be using a very unusual postgresql-specific syntax which > is rolling the whole inner row into a single-column tuple and then > referencing it that way, which is odd because the row itself is already a > tuple, not sure what forcing it into a tuple that contains exactly one tuple > accomplishes here. > > While we can certainly use tuple_() and probably create some extra custom > constructs around it to support this syntax, I see nothing in this specific > statement to justify the use of these syntaxes, nor the need to produce > correlated scalar subqueries when we just are looking for one row from the > "certificate" table. I don't at the moment see why you can't use this > statement, which is easier to read, platform agnostic, and way more > efficient: > > SELECT c.csr_id, c.not_before, c.not_after FROM certificate AS c JOIN csr ON > csr.id = c.csr_id ORDER BY c.not_after DESC LIMIT 1 > > > > > > > > > > > There ought to be something, but for the life of me I can't seem to make it > happen. > > InklessPen tried to help me on IRC, and we got something, but it's still not > quite the same, > https://gist.github.com/inklesspen/49e69e1f33f3852d348a > > A reduced model is below, > > > class CSR(Base): > certificates = _orm.relationship("Certificate", backref="csr") > > class Certificate(Base): > not_before = _sa.Column(_sa.DateTime, nullable=False) > not_after = _sa.Column(_sa.DateTime, nullable=False) > csr_id = _fkcolumn(CSR.id, nullable=False) > > > Regards, > D.S. > > -- > 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. > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/ooNUhATaTEU/unsubscribe. > To unsubscribe from this group and all its topics, 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. -- 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.
