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.

Answer 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

Answer 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__.

Answer 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)