On Tue, Feb 5, 2019 at 11:51 AM Ian Miller <irmille...@gmail.com> wrote: > > Hello all - > > I am relatively new to using SQLAlchemy for more complex use cases. I am in > the process of creating a time series query, but I am unable to reference a > column by its alias at the top level of the query. > > This is the query that I am trying to address that SQLAlchemy is currently > generating: > > SELECT non_interval_query.metadata_value AS non_interval_query_metadata_value, > coalesce(sum(non_interval_query.coalesce_2), 0) AS coalesce_1, > timestamp > FROM > (SELECT generate_series(date_trunc('day', > date('2019-01-06T00:00:00+00:00')), date_trunc('day', > date('2019-01-12T00:00:00+00:00')), '1 day') AS timestamp) AS time_series > LEFT OUTER JOIN > (SELECT post_metadata_1.metadata_value AS post_metadata_1_metadata_value, > post_metadata_2.metadata_value AS post_metadata_2_metadata_value, > vw_post.created_at AS vw_post_created_at, > coalesce(count(DISTINCT vw_post.id), 0) AS coalesce_1 > FROM vw_post > JOIN post_metadata AS post_metadata_1 ON post_metadata_1.post_id = > vw_post.id > JOIN post_metadata AS post_metadata_2 ON post_metadata_2.post_id = > vw_post.id > WHERE post_metadata_1.metadata_value IN ('<metadata_values>') > AND post_metadata_2.metadata_value IN ('<metadata_value>') > AND vw_post.created_at >= '2019-01-06T00:00:00+00:00' > AND vw_post.created_at <= '2019-01-12T00:00:00+00:00' > AND post_metadata_1.schema_uid = '<schema_uid>' > AND post_metadata_1.metadata_name = '<metadata_name>' > AND post_metadata_2.schema_uid = '<schema_uid>' > AND post_metadata_2.metadata_name = '<metadata_name>' > AND vw_post.license_id IN (<license_ids>) > GROUP BY vw_post.created_at, > post_metadata_1.metadata_value, > post_metadata_2.metadata_value, > vw_post.created_at) AS non_interval_query ON date_trunc('day', > created_at) = timestamp; > > You'll notice that "non_interval_query.metadata_value AS > non_interval_query_metadata_value" specified at the beginning of the query is > ambiguous due to the 2 "metadata_value" selects in the "non_interval_query" > subquery. What I'm trying to do is have 2 selects at the top level - one for > "non_interval_query.post_metadata_1_metadata_value" and one for > "non_interval_query.post_metadata_2_metadata_value". > > > For reference, here is the code used to generate the above query:
so this code is incomplete, referring to something called "_prepare_non_gb_columns" which is likely where this is going wrong, the subquery that you SELECT from has a .c namespace from which you would be selecting both non_interval_query.c.post_metadata_1_metadata_value and non_interval_query.c.post_metadata_2_metadata_value from, separately. The names that are available on .c. come directly from the label names you use in the subquery, like metadata_value.label("post_metadata_1_metadata_value"). I mocked the important part there up as a script below, but I did it in Core which is easier for this kind of query, but then for demonstration I adapted it to Query as well. long term plan is to unify these two query interfaces more completely. Core: from sqlalchemy import table, column, select post_metadata = table( "post_metadata", column("post_id"), column("metadata_value") ) vw_post = table("vw_post", column("id")) post_metadata_1 = post_metadata.alias("post_metadata_1") post_metadata_2 = post_metadata.alias("post_metadata_2") non_interval_query = ( select( [ post_metadata_1.c.metadata_value.label( "post_metadata_1_metadata_value" ), post_metadata_2.c.metadata_value.label( "post_metadata_2_metadata_value" ), ] ) .select_from( vw_post.join( post_metadata_1, post_metadata_1.c.post_id == vw_post.c.id ).join(post_metadata_2, post_metadata_2.c.post_id == vw_post.c.id) ) .alias("non_interval_query") ) stmt = select( [ non_interval_query.c.post_metadata_1_metadata_value, non_interval_query.c.post_metadata_2_metadata_value, ] ).apply_labels() print(stmt) ORM version: from sqlalchemy import table, column, select post_metadata = table( "post_metadata", column("post_id"), column("metadata_value") ) vw_post = table("vw_post", column("id")) from sqlalchemy.orm import Session, aliased s = Session() post_metadata_1 = aliased(post_metadata, "post_metadata_1") post_metadata_2 = aliased(post_metadata, "post_metadata_2") non_interval_query = ( s.query( post_metadata_1.c.metadata_value.label( "post_metadata_1_metadata_value" ), post_metadata_2.c.metadata_value.label( "post_metadata_2_metadata_value" ), ) .select_from(vw_post) .join(post_metadata_1, post_metadata_1.c.post_id == vw_post.c.id) .join(post_metadata_2, post_metadata_2.c.post_id == vw_post.c.id) .subquery("non_interval_query") ) stmt = s.query( non_interval_query.c.post_metadata_1_metadata_value, non_interval_query.c.post_metadata_2_metadata_value, ) print(stmt) > > > def apply_date_group_by(self, session, query, range_gb_params): > field_name = self.db.get("column") > model = self._object.get("model") > > if not field_name or not model: > raise ValueError("Invalid date group by") > > gb_column = self._build_column() > interval = range_gb_params.get("interval") > interval_type = range_gb_params.get("interval_type") > > time_series = func.generate_series( > func.date_trunc(interval_type, func.date(range_gb_params["start"])), > func.date_trunc(interval_type, func.date(range_gb_params["end"])), > interval, > ).label("timestamp") > > ts_column = column("timestamp") > > time_series_query = session.query(time_series).subquery("time_series") > non_interval_query = query.subquery("non_interval_query") > # have to replace the original gb_column with the 'timestamp' column > # in order to properly merge the dataset into the time series dataset > non_gb_columns, gbs = self._prepare_non_gb_columns( > ts_column, gb_column, non_interval_query.columns > ) > > # construct query with correct position passed in from `range_gb_params` > query_position = range_gb_params.get("query_index_position", 0) > non_gb_columns.insert(query_position, ts_column) > > date_gb_query = session.query(*non_gb_columns).select_from( > time_series_query.outerjoin( > non_interval_query, > func.date_trunc(interval_type, column(field_name)) == ts_column, > ) > ) > > if gbs: > date_gb_query = date_gb_query.group_by(*gbs) > > return date_gb_query.order_by(ts_column) > > > > Any help on this would be greatly appreciated! > > -- > 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.