Hi everyone,
I have a database of some `items` which have a range (tsarange =>
DateTimeRange) column that usually ranges in intervals of a bunch of
seconds and I basically want to create an availability statistics of those
items (based on their `lower(Item.range)`) for some period (month/day) in
some given interval (last year, last half of a year or custom).
This is obviously not a hard task to do, one just needs to extract the
month or day, that he wants and group by the extracted part, however in a
database with a ~3.10^6 rows this takes around 8-9s which is not really
good for user experience hence I came up with a bit more complicated query
which results in the same thing but taking only 800ms.
Let's say for example that we want to know how many `items` there are per
day in the whole year, the query would look followingly:
```
SELECT day.date, count(i.id)
FROM (
SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
AS date
FROM generate_series(0, 365, 1) AS offs
) AS day
LEFT OUTER JOIN items AS i
ON (day.date=to_char(date_trunc('day', lower(i.range)), 'YYYY-MM-DD'))
GROUP BY day.date;
```
My issue is that I am not able to reproduce this query into SQLAlchemy
query builder.
Specifically I am struggling with this part
`(current_date - offs)`
This may seem harmless at first sight rewritten in the following form
```
series = alias(func.generate_series(0, 365, 1))
subq = sess.query(func.to_char(func.date_trunc('day', (func.current_date()
- series)), 'YYYY-MM-DD')).\
select_from(series).\
subquery()
```
but SQLAlchemy is trying to cast `series` to a scalar due to the
comparison, which is obviously not possible and available thus failing and
yielding an error that `generate_series` doesn't have a `as_scalar()`
method.
Is it even possible to rewrite this query?
I hope so. I am using SQLAlchemy ORM without any additional framework.
I will appreciate any help.
--
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
