title: How to paginate in Flask-SQLAlchemy for db.session joined queries?
body: <p>Say, we have the following relationships:</p>
  
  <ul>
  <li>a person can have many email addresses</li>
  <li>a email service provider can (obviously) serve multiple email address</li>
  </ul>
  
  <p>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.</p>
  
  <p>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.)</p>
  
  <p>I figured out how to do this with the following subqueries (following the sqlalchemy tutorial):</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>This works okay (it returns a 4-tuple of <code>(Provider, user_id, provider_id, email_address)</code>, all the information that I want), but I later found out this is <strong>not</strong> using the Flask <code>BaseQuery</code> class, so that <code>pagination</code> provided by Flask-SQLAlchemy does not work. Apparently <code>db.session.query()</code> is not the Flask-SQLAlchemy Query instance.</p>
  
  <p>I tried to do <code>Emails.query.outerjoin[...]</code> but that returns only columns in the email table though I want both the provider info and the emails.</p>
  
  <p><strong>My question</strong>: how can I do the same thing with Flask-SQLAlchemy so that I do not have to re-implement pagination that is already there? </p>
  
  <hr>
  
  <p>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 <strong>proper</strong> way of doing this.</p>