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)

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

Answer 5

How do you init your application with SQLAlchemy?

Probably your current SQLAlchemy connection has nothing to do with flask.ext.sqalchemy and you use original sqlalchemy

Check this tutorial and check your imports, that they really come from flask.ext.sqlalchemy

http://pythonhosted.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application

Answer 6

You can try to paginate the list with results.

my_list = [my_list[i:i + per_page] for i in range(0, len(my_list), per_page)][page]

Answer 7

I did this and it works:

query = db.session.query(Table1, Table2, ...).filter(...)

if page_size is not None:
   query = query.limit(page_size)
if page is not None:
   query = query.offset(page*page_size)
query = query.all()

Answer 8

I could be wrong, but I think your problem may be the .all(). By using that, you're getting a list, not a query object.

Try leaving it off, and pass your query to the pagination method like so (I left off all the subquery details for clarity's sake):

email_query = db.session.query(Emails).filter(**filters)
email_query.paginate(page, per_page)