CODEWITHSUNDEEP
djangodjango-orm
emihir0
emihir0

Reputation: 1250

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

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?

Upvotes: 3

Views: 7476

Answers (3)

Sagar
Sagar

Reputation: 1155

try this:

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

Upvotes: 1

Milano
Milano

Reputation: 18735

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.

Upvotes: 0

Nam Nguyễn
Nam Nguyễn

Reputation: 597

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

Upvotes: 0

Related Questions