Re: [sqlalchemy] Extra table name in FROM clause

[sector119]

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

Re: [sqlalchemy] Extra table name in FROM clause

[Mike Bayer]

On Thu, Jan 4, 2018 at 4:08 PM, sector119  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 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.

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

[sqlalchemy] Extra table name in FROM clause

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