Here is SQL that works as expected:
SELECT
m.*,
d.*
FROM (
SELECT
person_id,
service_id,
person_id_internal,
meters_readings, -- two-dimensional array
meters_readings [generate_subscripts(meters_readings, 1)] [1] AS meter_id,
meters_readings [generate_subscripts(meters_readings, 1)] [2] AS
organization_reading,
date
FROM
databases
) d LEFT OUTER JOIN LATERAL (
SELECT DISTINCT ON (service_id, person_id_internal, meter_id)
user_id,
reading
FROM meter_readings
WHERE
person_id = d.person_id AND
service_id = d.service_id AND
meter_id = d.meter_id AND
commit_date > d.date AND
rollback_date IS NULL AND
reading IS NOT NULL
ORDER BY
service_id,
person_id_internal,
meter_id,
commit_date DESC,
commit_time DESC
) m ON TRUE
WHERE
d.person_id = 2099000301470;
And that is SA query that also works :) I managed to write it ))
def get_person_meters_q(dbsession, person_id):
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()
q = dbsession.query(
meter_readings,
database
). \
select_from(
database.outerjoin(meter_readings, true())
).filter(database.c.person_id == person_id)
return q
The problem with extra "database" table name in select was because I use
filter(Database.person_id == person_id)
but not filter(database.c.person_id == person_id)
About LATERAL
https://www.postgresql.org/docs/current/static/sql-select.html
The LATERAL key word can precede a sub-SELECT FROM item.
This allows the sub-SELECT to *refer to columns of FROM items **that appear
before it* in the FROM list.
(Without LATERAL, each sub-SELECT is evaluated independently and so cannot
cross-reference any other FROM item.)
So I refer columns that are in database query (first) from meter_readings query
(second select)
Without LATERAL I get:
ERROR: invalid reference to FROM-clause entry for table "d"
Hint: There is an entry for table "d", but it cannot be referenced from this
part of the query.
And that is SQL query result:
\N \N 2099000153759 75 952160 {{952160,1140,0}} 952160
1140 2017-11-30
\N \N 2099000153759 2 27852 {{219935,14768,0}} 219935
14768 2017-11-30
\N \N 2099000153759 4 206688 {{468805,57,0},{589164,73,0}}
468805 57 2017-11-30
\N \N 2099000153759 4 206688 {{468805,57,0},{589164,73,0}}
589164 73 2017-11-30
--
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.
