How to use subqueries with Flask-SqlAlchemy?

Question

query_1 = db\
    .Query([UserModel, func.count(FriendModel.friend_id)])\
    .select_from(UserModel)\
    .outerjoin(FriendModel, and_(UserModel.id==FriendModel.user_id))\
    .group_by(FriendModel.user_id)
  s_1 = query_1.subquery('s_1')
  print s_1.c.id
  query_2 = db\
    .Query(FriendModel)\
    .select_from(FriendModel)\
    .outerjoin(s_1, FriendModel.user_id==s_1.c.id)

Help to get two queries together. https://gist.github.com/vlikin/17d53440eeef7f4147b2

I receive such errors:
InvalidRequestError: SQL expression, column, or mapped entity expected - got '[<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f2e28e1b810>, <sqlalchemy.sql.functions.count at 0x7f2e236c3ed0; count>]'

or

AttributeError: Neither ‘count’ object nor ‘Comparator’ object has an attribute ‘_autoflush’

Also I have problems because of db.Query instead db.session.query. I want to use db.Query because it has paginate :)

Thank you.

@zwirbeltier: as the question is not really clear, what is the real objective? — van
– van, Commented Mar 23, 2015 at 7:11
I've edited the title to express what I think the question asks: "How to use subqueries with Flask-SqlAlchemy?". — zwirbeltier
– zwirbeltier, Commented Mar 23, 2015 at 10:30
Sorry. The main question is not related with the subqueries. It is related with the paginator. If you make run a subquery with a paginator method. You will resolve the issue. Thank you. — Victor Shelepen
– Victor Shelepen, Commented Mar 23, 2015 at 10:50
@viktor.likin: the point is that exactly that doesn't work. Because db.Query from flask-sqlalchemy doesn't seem to work with subquery and db.session.query (from sqlalchemy) doesn't have the paginate() method. — zwirbeltier
– zwirbeltier, Commented Mar 23, 2015 at 12:06

zwirbeltier · Accepted Answer · 2015-03-26 12:11:26Z

3

I was having the exact same issue.

My solution to this problem is that I use db.session.query directly and create a new subclass of flask_sqlalchemy.Pagination. The subclass includes a method that allows me to create a Pagination object from any query.

from flask import abort
from flask_sqlalchemy import Pagination as _Pagination

class Pagination(_Pagination):
    """Extend Pagination class to let us construct it from any BaseQuery."""
    @classmethod
    def from_query(cls, query, page, per_page, error_out=True):
        """Returns `per_page` items from page `page`.  By default it will
        abort with 404 if no items were found and the page was larger than
        1.  This behavor can be disabled by setting `error_out` to `False`.

        This is basically a copy of flask_sqlalchemy.Query.paginate()

        Returns an :class:`Pagination` object.
        """
        if error_out and page < 1:
            abort(404)
        items = query.limit(per_page).offset((page - 1) * per_page).all()
        if not items and page != 1 and error_out:
            abort(404)

        # No need to count if we're on the first page and there are fewer
        # items than we expected.
        if page == 1 and len(items) < per_page:
            total = len(items)
        else:
            total = query.order_by(None).count()

        return cls(query, page, per_page, total, items)

Now use this with according to the following snippet:

query = db.session.query(…)
pagination = Pagination.from_query(query, page, 20)

The problem in the first place is that the main logic to create a Pagination-object from a db.Query is not part of the Pagination class but implemented in db.Query.paginate().

answered Mar 26, 2015 at 12:11

zwirbeltier

9569 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Victor Shelepen Over a year ago

It seems you need get a pull request to the Flask project. github.com/mitsuhiko/flask

zwirbeltier Over a year ago

Yes, I guess I will create a pull-request as soon as I come around to it.

zwirbeltier Over a year ago

There is already a pull-request at github.com/mitsuhiko/flask-sqlalchemy/pull/265

Collectives™ on Stack Overflow

How to use subqueries with Flask-SqlAlchemy?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related