SQLAlchemy filter with many-to-many relations-CodePudding

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?

CodePudding user response：

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:

q = session.query(
    ProductRequest
).join(
    ProductRequest.tags
).join(
    Tag.stores
).filter(
    Store.id == store.id)

product_requests_for_store = q.all()

With a schema like this:

stores_tags_t = Table(
    "stores_tags",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("store_id", Integer, ForeignKey("stores.id")),
    Column("tag_id", Integer, ForeignKey("tags.id")),
)

product_requests_tags_t = Table(
    "product_request_tags",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("product_request_id", Integer, ForeignKey("product_requests.id")),
    Column("tag_id", Integer, ForeignKey("tags.id")),
)

class Store(Base):
    __tablename__ = "stores"
    id = Column(Integer, primary_key=True)
    name = Column(String(), unique=True, index=True)
    tags = relationship('Tag', secondary=stores_tags_t, backref=backref('stores'))

class ProductRequest(Base):
    __tablename__ = "product_requests"
    id = Column(Integer, primary_key=True)
    name = Column(String(), unique=True, index=True)
    tags = relationship('Tag', secondary=product_requests_tags_t, backref=backref('product_requests'))

class Tag(Base):
    __tablename__ = "tags"
    id = Column(Integer, primary_key=True)
    name = Column(String())

CodePudding user response：

This worked:

session.query(ProductRequest).filter( ProductRequest.product_tags.any(Tag.id.in_(store_tag.id for store_tag in store_tags) ) ).all()