CODEWITHSUNDEEP
pythonsqlalchemyorm
pepegc
pepegc

Reputation: 123

Complex filter in joined tables SQLAlchemy

I have something like the following query

query = db.session.query(Attribute)
    .select_from(Product)
    .filter_by(category_id=some_id)
    .join(Attribute)

Which returns:

[(<Product 1>, <Attribute 1>),
 (<Product 1>, <Attribute 3>), #two attributes for Product 1
 (<Product 2>, <Attribute 2>),
 (<Product 2>, <Attribute 5>), #two attributes for Product 2
 ...]

I need a query that retains all the Product that have a least one Attribute matching certain type and value values. In the example above, if <Attribute 1> was the only Attribute meeting those conditions, the result should be:

[(<Product 1>, <Attribute 1>), #Attribute1 met the conditions
 (<Product 1>, <Attribute 3>)] #Other Product1 rows are thus kept

Edit: Product and Attribute have a many to many relationship, here are the models:

class Product(db.Model):
     id = db.Column(db.Integer, primary_key=True)
     sku = db.Column(db.String(10), unique=True, nullable=False)
     category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False)
     category = db.relationship('Category', backref=db.backref('products', lazy=True))

class ProductAttribute(db.Model):
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), primary_key=True)
    attribute_id = db.Column(db.Integer, db.ForeignKey('attribute.id'), primary_key

class Attribute(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    type = db.String(db.String(40))
    value = db.String(db.String(40))

Upvotes: 3

Views: 212

Answers (1)

van
van

Reputation: 76962

One way to solve this is to use the relationship.any construct.

Please find below the fully working sample code, where i tried to guess a more complete model. Even if it is not complete, it will give you a hint in the right direction:

Model:

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    sku = db.Column(db.String(10), unique=True, nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False)
    category = db.relationship('Category', backref=db.backref('products', lazy=True))
    # added this relationship definition
    attributes = db.relationship("Attribute", secondary="product_attribute")


class ProductAttribute(db.Model):
    product_id = db.Column(db.Integer, db.ForeignKey("product.id"), primary_key=True)
    attribute_id = db.Column(
        db.Integer, db.ForeignKey("attribute.id"), primary_key=True
    )


class Attribute(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(40))
    value = db.Column(db.String(40))

Test Data:

attrs = ac_b, ac_w, as_l, as_s, ah_y, ah_n = [
    Attribute(type="color", value="black"),
    Attribute(type="color", value="green"),
    Attribute(type="size", value="large"),
    Attribute(type="size", value="small"),
    Attribute(type="healthy", value="yes"),
    Attribute(type="healthy", value="not that much"),
]

prods = [
    Product(sku="no-attribs", attributes=[]),
    Product(sku="black-only", attributes=[ac_b]),
    Product(sku="black-larg", attributes=[ac_b, as_l]),
    Product(sku="white-col", attributes=[ac_w, as_s]),
    Product(sku="no-colors", attributes=[ah_y, as_s]),
]

db.session.add_all(attrs + prods)

ANSWER: the Query

query = (
    db.session.query(Product, Attribute)
    # .select_from(Product)  # this could be removed if `Product` is included in the query above
    .join(Attribute, Product.attributes)
    # .filter_by(category_id=some_id)  # this one can be added, but not used in tests
    .filter(Product.attributes.any(
        # HERE you can put any combination of filters on the 'Attribute' you need
        and_(
            Attribute.type == 'color',
            Attribute.value == 'black',
        )
    ))
)

Resulting SQL: On postgres it should look like below:

SELECT product.id,
       product.sku,
       attribute.id,
       attribute.type,
       attribute.value
FROM product
JOIN product_attribute AS product_attribute_1 ON product.id = product_attribute_1.product_id
JOIN attribute ON attribute.id = product_attribute_1.attribute_id
WHERE EXISTS
    (SELECT 1
     FROM product_attribute,
          attribute
     WHERE product.id = product_attribute.product_id
       AND attribute.id = product_attribute.attribute_id
       AND attribute.type = %(type_1)s
       AND attribute.value = %(value_1)s)

Results of the query:

As can be seen below, from the test data the results cover all attributes are included for all product which fit the requirement (have "color" = "black"):

(<Product(id=3, sku='black-larg')>, <Attribute(id=1, type='color', value='black')>)
(<Product(id=3, sku='black-larg')>, <Attribute(id=3, type='size', value='large')>)
(<Product(id=2, sku='black-only')>, <Attribute(id=1, type='color', value='black')>)

Upvotes: 2

Related Questions