Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 214
Alexander Chen Avatar asked Mar 31 '13 06:03

Alexander Chen


3 Answers

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

like image 54
Alexander Chen Avatar answered Oct 19 '22 12:10

Alexander Chen


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

like image 31
user1431368 Avatar answered Oct 19 '22 12:10

user1431368


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)
like image 42
Joe Jean JJ Avatar answered Oct 19 '22 10:10

Joe Jean JJ