python - SQLAlchemy: Filtering across many-to-many yet returning all results -

python - SQLAlchemy: Filtering across many-to-many yet returning all results -

starting many-to-many blog , tag model , query querying many-to-many relationship in sqlalchemy, know session.query(blog).join(blog.tags).options(contains_eager(blog.tags)).filter(blog.tags.in_(list_of_relevant_tags)).all() give me blog posts have @ to the lowest degree 1 of tags in list_of_relevant_tags, , tags have been queried , returned.

the potential problem see (i'm having problem testing this) if want list of tags on returned blogs. want homecoming tags, generated sql suggest that, @ best, require sec query. @ worst, sqla homecoming tags matched list.

is there way submit query in orm homecoming matching blog posts along all tags in 1 database hit?

i figured out. feels workaround, though; there's gotta more direct way orm.

basically, utilize subquery of association table (let's phone call blogtag columns blog_id , tag_id, foreign keys blogs.id , tags.id, respectively) generate list of blog_ids meet tag criteria. inner bring together list of blog_ids otherwise-unfiltered query of blog joined tag homecoming relevant blogs.

t = session.query(blogtag.blog_id).filter(blogtag.tag_id.in_(list_of_relevant_tags)).\ group_by(blogtag.blog_id).subquery('t') blogs = session.query(blog).join(blog.tags).join(t, t.c.blog_id == blog.id).\ options(contains_eager(blog.tags)).all()

python postgresql python-2.7 sqlalchemy