How to paginate in Flask-SQLAlchemy for db.session

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

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]