oops, .filter(database.c.person_id == person_id) fixed the problem. Thanks!
четверг, 4 января 2018 г., 23:08:35 UTC+2 пользователь sector119 написал: > > 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 > > > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.