On Mon, Nov 12, 2018 at 10:07 AM Mike Bayer <[email protected]> wrote: > > On Mon, Nov 12, 2018 at 7:56 AM andrew cooke <[email protected]> wrote: > > > > > > 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; > > > > this query is not using any correlation, it has no subqueries. > > > 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
oh wait, the problem is that outerjoin() method you're using. that creates a join to the SELECT as a whole and generates a subquery. That's not appropriate here. here's how to join: select([x, y, z]).select_from(sj.outerjoin(sjf).outerjoin(sji)) see the tutorial at https://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-joins . > > > > 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. > > can you remove the correlate_except? this is a straightforward > query, however I can't be sure what the structure of "sjf" is, are > these all Table objects or are you mapping to selectables? there's > no reason I can see which would generate that additional FROM clause. > > > > > > 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. -- 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.
