Using flask-sqlalchemy BaseQuery and Pagination with multiple tables.

[Mark S]

Hi 

I can successfully use pagination with the following - 

mydata=Article.query.filter(Article.author_id==User.id).filter(User.id==g.user.id).paginate(page, POSTS_PER_PAGE, False)

However, I need to fetch columns from multiple tables. In that case how can I modify the code above in order to use pagination? 

Here is what I need to do - 

mydata = db.session.query("id","title","Author").from_statement("\

                                                     SELECT a.id,a.title,u.author \

                                                     FROM article a, user u\

                                                     where a.user_id=u.id \

                                                     and u.id=:userid")\

                                                     .params(userid=g.user.id).all()

However, with this , pagination does not work and I get an error - AttributeError: 'Query' object has no attribute 'paginate'

Can you please help?

[Simon King]

There are a couple of options. One would be to configure your
"db.session" object to use the Flask-sqlalchemy query class, rather
than the default SQLAlchemy one. The Session class constructor has a
query_cls parameter for this purpose - you'd want to pass
flask_sqlalchemy.BaseQuery. I don't use Flask, so I don't know how
your session is currently being configured.

Another option would be to use the with_entities method of Query to
change the set of columns that are being queried for:

http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.with_entities

so you could write something like "Article.query.with_entities('id',
'title', 'Author').from_statement(...)"

But I guess what you are really trying to do is to query Articles, but
only to load certain columns. You might be interested in using
"load_only" instead:

http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#load-only-cols

which is part of a bigger topic about deferred column loading:

http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-column-loading

Hope that helps,

Simon