Can I have SQLAlchemy do subquery eager loading without repeating the full original query?

Question

Suppose we have original generated query like that:

SELECT company.x AS company_x, ...
FROM company
LEFT OUTER JOIN acc ON acc.id = company.acc
LEFT OUTER JOIN usercomp_links ON company.id = usercomp_links.pid
LEFT OUTER JOIN usergro_links ON acc.id = usergro_links.pid
WHERE usergro_links.eid = %s OR usercomp_links.eid = %s

And if we add .options(subqueryload(Company.childs)) to this, we will get:

SELECT company.x AS company_x, ..., anon_1.company_id AS anon_1_company_id
FROM (
    SELECT company.id AS company_id
    FROM company
    LEFT OUTER JOIN acc ON acc.id = company.acc
    LEFT OUTER JOIN usercomp_links ON company.id = usercomp_links.pid
    LEFT OUTER JOIN usergro_links ON acc.id = usergro_links.pid
    WHERE usergro_links.eid = %s OR usercomp_links.eid = %s) AS anon_1
INNER JOIN acel_links AS acel_links_1 ON anon_1.company_id = acel_links_1.eid
INNER JOIN company ON company.id = acel_links_1.pid ORDER BY anon_1.company_id

And this is sloooow. If I will get company ids from first query, and load all child companies by hands, it will be blazingly fast in compare to what we get in this case.

I have read documentation, looked at the code, but dont see if I can tell sqlalchemy just get ids from results of first query and load childs in separate, comparatively simple query. I dont rely on this sample - I had more more harder situations, when sqlalchemy just cant load constructed query. And why to do all this work from first query one more time?

So anyone know how to eager load without auto constructed "join from join in join" style?

Answer 1

http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.selectinload

It was added to sqlalchemy, so now you can just use selectinload strategy.