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 > > 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.
