I am currently using the following sqlalchemy code, _query = super().get_query(model, info, sort, **args) query = _query.group_by( func.date_format(DataModel.timestamp, "%Y-%m-%d %H") ) return query
I am trying to aggregate the the max value of a field from DataModel.value utilizing the group by clause. In simple sql, you would do the following. SELECT max(data.value) AS data_value from data GROUP BY date_format(data.timestamp, "%Y-%m-%d %H") What would the proper way to express this? The various methods I have tried somehow overwrite the original query and do not map to our attributes. I have some additional context on the issue 1. We are using the graphene-sqlalchemy package. 2. When you do a GraphQL web api query, the package creates a sqlalchemy.orm.query.Query object. 3. We want to modify this standard query that the package creates so that we can do the group_by action to help us get the maximum time series value for every hour because this is not possible using GraphQL. 4. Talking to the graphene-sqlalchemy team they told us the best place to do the modification to the standardized query is in the get_query method (line 67) in this file: https://github.com/graphql- python/graphene-sqlalchemy/blob/master/graphene_sqlalchemy/fields.py <https://github.com/graphql-python/graphene-sqlalchemy/blob/master/graphene_sqlalchemy/fields.py> 5. The standardized query we need to modify translates to this SQL statement: SELECT sy_data.oid_id, sy_data.rrd_timestamp, sy_data.cabinet_id, sy_data .customer_id, sy_data.value, sy_data.active_flag, sy_data.timestamp FROM sy_data WHERE sy_data.rrd_timestamp >= %(rrd_timestamp_1)s AND sy_data.rrd_timestamp <= %(rrd_timestamp_2)s AND (sy_data.oid_id = %(oid_id_1)s OR sy_data.oid_id = %(o id_id_2)s) ORDER BY sy_data.oid_id ASC, sy_data.rrd_timestamp ASC Therefore we need to find a way to insert a func.max for the values in the first part of the SELECT statement, (before the FROM). It is easy for us to apend the group_by like this. query.group_by( func.date_format(DataModel.timestamp, "%Y-%m-%d %H") ) The big issue for us is to figure out how to insert the func.max Getting a solution to this will help the graphene-sqlalchemy team create better documentation. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/742e96bc-f6cc-43ca-8a2e-697b3803301cn%40googlegroups.com.