CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Martin Fischer
Martin Fischer

Reputation: 639

SQLAlchemy Many-To-One relation query filter for column in list

I have following models defined:

class Attribute(Base):
    __tablename__ = "attributes"

    id = Column(BigInteger, primary_key=True, index=True)
    data_id = Column(BigInteger, ForeignKey("data.art_no"))
    name = Column(VARCHAR(500), index=True)

    data = relationship("Data", back_populates="attributes_rel")


class Data(Base):
    __tablename__ = "data"

    art_no = Column(BigInteger, primary_key=True, index=True)
    multiplier = Column(Float)
    attributes_rel = relationship("Attribute", back_populates="data", cascade="all, delete, delete-orphan")

@property
def attributes(self):
    return [attribute.name for attribute in self.attributes_rel]

If I query for Data rows, I get this rows (only attributes property:

#1 ['attributeX', 'attributeY']
#2 ['attributeZ']
#3 ['attributeX', 'attributeZ']

I want to do following thing now:

I have this list ['attributeX'] and I want to query my data and only get the Data rows back, which has the 'attributeX' attribute. If I have this list ['attributeX', 'attributeZ'], I want to query my data and only get the Data rows back, which has the 'attributeX' AND 'attributeZ' attribute.

How can I do the queries?

I tried .filter(models.Data.attributes_rel.any(models.Attribute.name.in_(attributesList))) which returns all rows which has any of the attribute from attributesList .... but I want only get the models.Data rows which has exactly the attributes from the list (or even others, too, but at least the ones from the list)

Optical sample of my issue:

enter image description here

This three attributes are associated to Data rows. I have set attributeList=['Test2','Test3'] ... but also the last row is returned .. because it has attribute Test3 but it should not be returned because it has not Test2 ... any idea?

Upvotes: 0

Views: 144

Answers (1)

van
van

Reputation: 76962

in_ will basically do any of the attributes is present, whereas what you want is ALL attributes are present.

To achieve this, just add filter for each attribute name separately:

q = session.query(Data)
for attr in attributesList:
    q = q.filter(Data.attributes_rel.any(Attribute.name == attr))

Upvotes: 1

Related Questions