A store can have many interests. User request a product that is tagged. Query required is to get the product requests that have tags shared with current store.
# in Store -> relationship('Tag', secondary=store_interest_tags, lazy='dynamic', backref=backref('store', lazy=True))
store_tags = store.interests
matched_requests_to_store = []
for tag in store_tags:
r = session.query(ProductRequest).filter(ProductRequest.product_tags.contains(tag)).all()
matched_requests_to_store.extend(r)
I am sure there might be a more efficient way to query that. I have tried the following:
session.query(ProductRequest).filter(ProductRequest.product_tags.any(store_tags)).all()
But got
psycopg2.errors.SyntaxError: subquery must return only one column
LINE 5: ..._id AND tag.id = product_requests_tags.tag_id AND (SELECT ta...
Any idea how to achieve such query?
2
Answers
This worked:
A query like this might work, I think it could be done with less joins but this is less rigid than dropping into using the secondary tables directly and specifying the individual joins:
With a schema like this: