Thank-you! I am sure that will be right - I was trying to find a "from" in the docs, but missed the select_from().
Thanks again, Andrew On Mon, Nov 12, 2018 at 10:15:05AM -0500, Mike Bayer wrote: > 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. > -- 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.
