Execute flask-SQLAlchemy subquery

Question

I want to execute the following subquery in flask-SQLAlchemy but don't know how:

SELECT * 
FROM (
     SELECT * 
     FROM  `articles` 
     WHERE publisher_id = "bild"
     ORDER BY date_time DESC 
     LIMIT 10
) AS t
ORDER BY RAND( ) 
LIMIT 2

I know I can build the query as:

subq = Article.query.filter(Article.publisher_id =='bild').order_by(Article.date_time.desc()).limit(10).subquery()
qry = subq.select().order_by(func.rand()).limit(2)

However I don't know how to execute it in the same fashion as I would execute e.g.

articles = Article.query.filter(Article.publisher_id =='bild').all()

i.e. to get all the Article objects. What I can do is call

db.session.execute(qry).fetchall()

but this only gives me a list with actual row values instead of the objects on which I could for example call another function (like article.to_json()).

Any ideas? qry is a sqlalchemy.sql.selectable.Select object and db.session.execute(qry) a sqlalchemy.engine.result.ResultProxy while Article.query, on which I could call all(), is a flask_sqlalchemy.BaseQuery. Thanks!!

Answer 1

You can use select_entity_from

qry = db.session.query(Article).select_entity_from(subq).order_by(func.rand()).limit(2)

or from_self

Article.query.filter(Article.publisher_id =='bild')\
             .order_by(Article.date_time.desc())\
             .limit(10)\
             .from_self()\
             .order_by(func.rand())\
             .limit(2)