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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
