This other question says how to use the OVER clause on sqlalchemy:
Using the OVER window function in SQLAlchemy
But how to do that using ORM? I have something like:
q = self.session.query(self.entity, func.count().over().label('count_over'))
This fails when I call q.all() with the following message:
sqlalchemy.exc.InvalidRequestError:
Ambiguous column name 'count(*) OVER ()' in result set! try 'use_labels' option on select statement
How can I solve this?
You have the over syntax almost correct, it should be something like this:
import sqlalchemy
q = self.session.query(
self.entity,
sqlalchemy.over(func.count()).label('count_over'),
)
Example from the docs:
from sqlalchemy import over
over(func.row_number(), order_by='x')
SQLAlchemy Query object has with_entities method that can be used to customize the list of columns the query returns:
Model.query.with_entities(Model.foo, func.count().over().label('count_over'))
Resulting in following SQL:
SELECT models.foo AS models_foo, count(*) OVER () AS count_over FROM models
You got the functions right. They way to use them to produce the desired result would be as follows:
from sqlalchemy import func
q = self.session.query(self.entity, func.count(self.entity).over().label('count_over'))
This will produce a COUNT(*) statement since no Entity.field was specified. I use the following format:
from myschema import MyEntity
from sqlalchemy import func
q = self.session.query(MyEntity, func.count(MyEntity.id).over().label('count'))
That is if there is an id field, of course. But you get the mechanics :-)
