On Thu, Jan 4, 2018 at 4:08 PM, sector119 <[email protected]> wrote: > Hello, > > In this query I get extra (unexpected) "databases" table name in FROM clause > > q = dbsession.query( > meter_readings.c.reading, > database.c.service_id > ). \ > select_from( > database.outerjoin(meter_readings, true()) > ).filter(Database.person_id == person_id) > > > Here is the query: > SELECT ... > FROM databases, (SELECT ... FROM databases) AS anon_2 LEFT OUTER JOIN > LATERAL (SELECT ... FROM meter_readings) AS anon_1 ON true WHERE > databases.person_id = 123 > > But I expect to get it without databases table like > SELECT ... FROM (SELECT ... FROM databases) AS anon_2 LEFT OUTER JOIN > LATERAL (SELECT ... FROM meter_readings) AS anon_1 ON true WHERE > databases.person_id = 123
you are filtering by Database.person_id which introduces the "databases" selectable as a FROM clause. I've only worked a little bit with the LATERAL thing but it doesn't seem that the query you are going for makes sense, as the "databases" part is on the left side of the join and isn't part of the LATERAL aspect of it; see https://www.postgresql.org/docs/9.4/static/queries-table-expressions.html where it says "Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. "- your "databases" subquery is not preceded by the LATERAL keyword, only the one dealing with meter_readings is. Can you please copy and paste the full SQL you want, and then run it directly in your Postgresql database to ensure that PG understands this query fully ? if PG understands it please send me the *exact* SQL that has shown to run, thanks. > > > What am I doing wrong? > > > > database = dbsession.query( > Database.person_id, > Database.service_id, > Database.person_id_internal, > Database.date, > ( > type_coerce( > Database.meters_readings[ > func.generate_subscripts(Database.meters_readings, 1) > ], > type_=ARRAY(Text) > )[1] > ).label('meter_id'), > ( > type_coerce( > Database.meters_readings[ > func.generate_subscripts(Database.meters_readings, 1) > ], > type_=ARRAY(Text) > )[2] > ).label('organization_reading') > ).subquery() > > meter_readings = dbsession.query( > MeterReading.user_id, > MeterReading.reading > ). \ > distinct( > MeterReading.service_id, > MeterReading.person_id_internal, > MeterReading.meter_id). \ > filter( > and_( > MeterReading.person_id == database.c.person_id, > MeterReading.service_id == database.c.service_id, > MeterReading.meter_id == database.c.meter_id, > MeterReading.commit_date > database.c.date, > MeterReading.rollback_date == None, > MeterReading.reading != None > ) > ). \ > order_by( > MeterReading.service_id, > MeterReading.person_id_internal, > MeterReading.meter_id, > MeterReading.commit_date.desc(), > MeterReading.commit_time.desc() > ).subquery().lateral() > > > -- > 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.
