1

I'm wanting to do pagination, I'm using PostgreSQL. To avoid doing the query twice, I'm using the feature described at https://stackoverflow.com/a/8242764

How can I add the full_count column to the list of columns fetched in my query. I can use query.add_columns, but that column is not accessible after calling query.all()

Is this possible in SQLAlchemy?

Community
  • 1
  • 1
axon
  • 688
  • 1
  • 7
  • 18

1 Answers1

4

Can you provide the original code for the query you want to augment? Are you using SQLAlchemy core or declarative ORM? For the latter it will look like this:

from sqlalchemy import func

items = session.query(Model, func.count().over().label('full_count')).all()
obj = items[0].Model
count = items[0].full_count
rkhayrov
  • 10,040
  • 2
  • 35
  • 40
  • I'm not sure what I was doing before, but it definitely wasn't what you've suggested. Your solution was exactly what I needed. – axon Aug 14 '13 at 14:36
  • 3
    Is there a way to do this where the 'full_count' field gets placed directly on the Model? So items[0] would return the same as items[0].Model in your example above, except that items[0].full_count would contain the result of the func.count()? – shroud Apr 09 '15 at 23:04
  • You may be able to get what you want using [query-time SQL expressions](https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html#query-time-sql-expressions-as-mapped-attributes). [Here's an example](https://stackoverflow.com/a/60908024/1749551). – Nick K9 Oct 17 '22 at 13:57