skip to Main Content

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


  1. Chosen as BEST ANSWER

    This worked:

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

  2. 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())
    
    
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search