Django - joining multiple tables (models) and filtering out based on their attribute

Question

I'm new to django and ORM in general, and so have trouble coming up with query which would join multiple tables.

I have 4 Models that need joining - Category, SubCategory, Product and Packaging, example values would be:

Category: 'male'

SubCategory: 'shoes'

Product: 'nikeXYZ'

Packaging: 'size_36: 1'

Each of the Model have FK to the model above (ie. SubCategory has field category etc).

My question is - how can I filter Product given a Category (e.g. male) and only show products which have Packaging attribute available set to True? Obviously I want to minimise the hits on my database (ideally do it with 1 SQL query).

I could do something along these lines:

available = Product.objects.filter(packaging__available=True) subcategories = SubCategory.objects.filter(category_id=<id_of_male>) products = available.filter(subcategory_id__in=subcategories)

but then that requires 2 hits on database at least (available, subcategories) I think. Is there a way to do it in one go?

What about something like this: Product.objects.filter(packaging__available=True,subcategories__category_id__in=[id_of_male]) - it isn't tested but I think that subcategories should be plural (related_name). — Milano
– Milano, Commented Jun 6, 2017 at 7:50

Raja Simon · Accepted Answer · 2017-06-06 09:08:48Z

1

try this:

lookup = {'packaging_available': True, 'subcategory__category_id__in': ['ids of males']} product_objs = Product.objects.filter(**lookup)

edited Jun 6, 2017 at 9:08

Raja Simon

10.3k5 gold badges46 silver badges77 bronze badges

answered Jun 6, 2017 at 7:58

Sagar

1,1653 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nam Nguyễn · Accepted Answer · 2017-06-06 07:53:13Z

Try to read: this You can query with _set, multi __ (to link models by FK) or create list ids

Milano · Accepted Answer · 2017-06-06 07:54:52Z

I think this should work but it's not tested:

Product.objects.filter(packaging__available=True,subcategori‌es__category_id__in=‌[id_of_male])

it isn't tested but I think that subcategories should be plural (related_name), if you didn't set related_name, then subcategory__set instead od subcategories should work.

Probably subcategori‌es__category_id__in=‌[id_of_male] can be switched to .._id=id_of_male.

Collectives™ on Stack Overflow

Django - joining multiple tables (models) and filtering out based on their attribute

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related