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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/742e96bc-f6cc-43ca-8a2e-697b3803301cn%40googlegroups.com.
