How to paginate in Flask-SQLAlchemy for db.session joined queries?

Question

Say, we have the following relationships:

• a person can have many email addresses
• a email service provider can (obviously) serve multiple email address

So, it's a many to many relationship. I have three tables: emails, providers, and users. Emails have two foreign ids for provider and user.

Now, given a specific person, I want to print all the email providers and the email address it hosts for this person, if it exists. (If the person do not have an email at Gmail, I still want Gmail be in the result. I believe otherwise I only need a left inner join to solve this.)

I figured out how to do this with the following subqueries (following the sqlalchemy tutorial):

email_subq = db.session.query(Emails).\
                filter(Emails.user_id==current_user.id).\
                subquery()

provider_and_email = db.session.query(Provider, email_subq).\
                outerjoin(email_subq, Provider.emails).\
                all()

This works okay (it returns a 4-tuple of (Provider, user_id, provider_id, email_address), all the information that I want), but I later found out this is not using the Flask BaseQuery class, so that pagination provided by Flask-SQLAlchemy does not work. Apparently db.session.query() is not the Flask-SQLAlchemy Query instance.

I tried to do Emails.query.outerjoin[...] but that returns only columns in the email table though I want both the provider info and the emails.

My question: how can I do the same thing with Flask-SQLAlchemy so that I do not have to re-implement pagination that is already there?

---

I guess the simplest option at this point is to implement my own paginate function, but I'd love to know if there is another proper way of doing this.

Solution 1

I'm not sure if this is going to end up being the long-term solution, and it does not directly address my concern about not using the Flask-SQLAlchemy's BaseQuery, but the most trivial way around to accomplish what I want is to reimplement the paginate function.

And, in fact, it is pretty easy to use the original Flask-SQLAlchemy routine to do this:

def paginate(query, page, per_page=20, error_out=True):
    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 Pagination(query, page, per_page, total, items)

Modified from the paginate function found around line 376: https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py

Solution 2

Your question is how to use Flask-SQLAlchemy's Pagination with regular SQLAlchemy queries.

Since Flask-SQLAlchemy's BaseQuery object holds no state of its own, and is derived from SQLAlchemy's Query, and is really just a container for methods, you can use this hack:

from flask.ext.sqlalchemy import BaseQuery
def paginate(sa_query, page, per_page=20, error_out=True):
  sa_query.__class__ = BaseQuery
  # We can now use BaseQuery methods like .paginate on our SA query
  return sa_query.paginate(page, per_page, error_out)

To use:

@route(...)
def provider_and_email_view(page):
  provider_and_email = db.session.query(...) # any SQLAlchemy query
  paginated_results = paginate(provider_and_email, page)
  return render_template('...', paginated_results=paginated_results)

*Edit:

Please be careful doing this. It's really just a way to avoid copying/pasting the paginate function, as seen in the other answer. Note that BaseQuery has no __init__ method. See How dangerous is setting self.__class__ to something else?.

*Edit2:

If BaseQuery had an __init__, you could construct one using the SA query object, rather than hacking .__class__.

Solution 3

Hey I have found a quick fix for this here it is:

provider_and_email = Provider.query.with_entities(email_subq).\
            outerjoin(email_subq, Provider.emails).paginate(page, POST_PER_PAGE_LONG, False)

Solution 4

I'm currently using this approach:

query = BaseQuery([Provider, email_subq], db.session())

to create my own BaseQuery. db is the SqlAlchemy instance.

Update: as @afilbert suggests you can also do this:

query = BaseQuery(provider_and_email.subquery(), db.session())

View more solutions

Comments

[Alexander Chen]

I did replace the all() with paginate() and proper args to it, so I am calling paginate on an sqlalchemy.orm.query.Query object (verified in Flask error page debugger). I guess this is not the source of error here. Sorry for not being clear in the question.

[Rachel Sanders]

Dang, I had hoped it was something easy. What happens if you use the Flask-SQLAlchemy query alias? Something like this: Email.query.filter(Emails.user_id==current_user.id).outerjoi‌​n(ProviderEmail, ProviderEmail.provider_id==Email.id)

[Alexander Chen]

It produces a SELECT query with only the fields in Email included, so nothing about the Provider is returned; at least it is the case the last time I tried.

[Alexander Chen]

I'm almost sure that I initiated Flask-SQLAlchemy the correct way. All the Modes.query commands work properly for me. And I indeed followed the above tutorial.

[Mark Hildreth]

Have you tried seeing if the query you build could be used to build a new BaseQuery object (similar to how you're creating a Paginate object) allowing you to reuse the BaseQuery paginate function?

[Alexander Chen]

Ah, yes. I suppose I could, but haven't tried it yet. Thanks for pointing it out.

[Joern Boegeholz]

I get sa_query.__class__ = BaseQuery TypeError: class assignment: only for heap types'

[afilbert]

Turns out you can take any SqlAlchemy Query object, too, and just call .subquery() method. So, using the OP's example: query_with_pagination = BaseQuery(provider_and_email.subquery(), db.session()) Yours should be the accepted answer, imo.

[Adversus]

@afilbert Others were posted in '13, added mine because I liked it better. Thanks for the moral support though ; )

[Abhishek]

paginate do not work with db.session.query(Table).filter(**condition).paginate(). This work only if we calling model class like Table.query.filter(**condition).paginate(). This answer is incorrect.