How to return multiple models/tables flask-sqlalchemy?

Question

I want to get data from multiple tables so I did this with sqlalchemy:

from flask.ext.sqlalchemy import SQLAlchemy
db = SQLAlchemy()
a = db.aliased(Employee, name='Employee')
b = db.aliased(Person, name='Person')
c = db.aliased(PositionFulfillment, name='PositionFulfillment')
d = db.aliased(Position, name='Position')
e = db.aliased(PositionType, name='PositionType')
f = db.aliased(Party, name='Party')
data = db.session.query(a, b, e, f) \
 .join(b, a.entity_id == b.entity_id) \
 .join(c, a.entity_id == c.party_id) \
 .join(d, c.position_id == d.id) \
 .join(e, d.position_type_id == e.id) \
 .join(f, d.party_id == f.id) \
 .all()

but then when i tried to use pagination with like this:

page = request.args.get('page', 1, type=int)
count = request.args.get('count', 10, type=int)
a = db.aliased(Employee, name='Employee')
b = db.aliased(Person, name='Person')
c = db.aliased(PositionFulfillment, name='PositionFulfillment')
d = db.aliased(Position, name='Position')
e = db.aliased(PositionType, name='PositionType')
f = db.aliased(Party, name='Party')
pagination = db.session.query(a, b, e, f) \
 .join(b, a.entity_id == b.entity_id) \
 .join(c, a.entity_id == c.party_id) \
 .join(d, c.position_id == d.id) \
 .join(e, d.position_type_id == e.id) \
 .join(f, d.party_id == f.id) \
 .paginate(page, per_page=count, error_out=False)
data = pagination.items

it gives this error:

AttributeError: 'Query' object has no attribute 'paginate'

according to this stackoverflow question

there's a difference between "query" that refers to to the SQLAlchemy Query and the BaseQuery that refers to the Flask-SQLALchemy BaseQuery, which happens to have the paginate() function.

if I want to use the paginate() function, I have to do my query like this,

Employee.query.join()....paginate(...)

but this will only return a list of data from Employee model, I want to be able to access data from the other tables/models too.

How does one query multiple tables and return data from each one or some of them with Flask-Sqalchemy so that i can use the paginate function?

Thanks,

• johnny: Can you post what your file structure looks like with the multiple models? Are they in different files?

Answer 1

As described in the other SO question you mention, you can only call the paginate() function on a BaseQuery object.

Employee.query.join()....paginate(...)

To access data from other tables, join the other tables and pass the desired columns to the add_columns() function.

Employee.query.join(Person).add_columns(Employee.id, Person.name).paginate(...)