I have some code that uses high-level ORM and it works fine, except that
one particular section is too slow. So I was trying to replace that
section with a lower level query. But I can't work out how to persuade the
Expression Language to stop auto-correlation.
The query I am trying to generate is:
select statistic_journal.id,
statistic_name.name,
coalesce(statistic_journal_float.value,
statistic_journal_integer.value)
from statistic_journal
join statistic_name
on statistic_journal.statistic_name_id = statistic_name.id
left outer join statistic_journal_float
on statistic_journal.id = statistic_journal_float.id
left outer join statistic_journal_integer
on statistic_journal.id = statistic_journal_integer.id
where statistic_name.owner = -19043;
But the closest I can get (only aiming for the outer join part of the
query) is:
Python 3.7.0 (default, Aug 20 2018, 18:32:46)
[GCC 7.3.1 20180323 [gcc-7-branch revision 258812]] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import inspect
>>> from sqlalchemy.sql.functions import coalesce
>>> from sqlalchemy.sql import select
>>> from ch2.squeal.tables.statistic import StatisticJournal, StatisticName,
StatisticJournalInteger, StatisticJournalFloat
>>>
>>> sj = inspect(StatisticJournal).local_table
>>> sji = inspect(StatisticJournalInteger).local_table
>>> sjf = inspect(StatisticJournalFloat).local_table
>>>
>>> print(select([sj.c.id,
... coalesce(sjf.c.value, sji.c.value)])
... .correlate_except(sji, sjf)
... .outerjoin(sjf).outerjoin(sji))
(SELECT statistic_journal.id AS id, coalesce(statistic_journal_float.value,
statistic_journal_integer.value) AS coalesce_1
FROM statistic_journal, statistic_journal_float, statistic_journal_integer)
LEFT OUTER JOIN statistic_journal_float ON id = statistic_journal_float.id
LEFT OUTER JOIN statistic_journal_integer ON id = statistic_journal_integer.
id
I realise this is not complete or runnable, but I hope it illustrates the
problem: the initial FROM includes statistic_journal_float and
statistic_journal_integer. I don't want those! They should come later. I
was hoping that the correlate_except() would remove them, but it doesn't
seem to have any effect.
Also, I am not sure if this is how you mix ORM and expression language
(explicitly getting the tables via inspect).
Is that clear? What am I doing wrong? This is SQLAlchemy 1.2.14 if it
makes any difference.
Thanks,
Andrew
--
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.
