[Answered ]-In this setup, how do I inner join Product, ProductAttributes, and ProductAttributesValue? [Django models & view]

by

1👍

✅

You can work with:

Product.objects.filter(
    categories=self.category,
    productattributesvalue__attributes__isnull=False
).values(
    'title',
    'productattributesvalue__attributes__name'
)

This will produce a QuerySet of dictionaries that contains the title and the name of the productattribute that is related to that product through the ProductAttributesValue junction table.

It might however be better to work with .annotate(…) [Django-doc] to obtain Products with an extra attribute .attr_name that contains the name of the product attributes. A Product will be repeated for each related ProductAttributes:

from django.db.models import F

Product.objects.filter(
    productattributesvalue__attribute__isnull=False
).annotate(
    attr_name=F('productattributesvalue__attribute__name')
)
👤willeM_ Van Onsem

0👍

let me put my 5 cents in. 🙂
I would advise you to use a ManyToMany relationship in implementation of your model. It will be a way easier for you to work with the model, and what is quite important, you can browse a numerous documentation guides dedicated to many-to-many relationship.

That is what your model is supposed to be, when speaking Django modelling language:

# models.py

class Attribute(models.Model):
    name = models.CharField(max_length=255)

class Product(models.Model):
    title = models.CharField(max_length=255)
    attributes = ManyToManyField(Attribute)

The relationship model ProductAttribute linking product_id with attribute_id would be created by Django automatically behind the scene.

As you can see you already have the inner join and it is absolutely free.
Other goodies and techniques can be found here:
https://docs.djangoproject.com/en/3.2/topics/db/examples/many_to_many/

👤alv2017

Leave a comment