1

I want to filter and get only those data related to its related objects data if only related to its parent object child objects have data. For eg: I have the following models:

class Collection(models.Model):
    date_of_collection=models.DateField()

class Product(models.Model):
    name=models.CharField(max_length=100)
    collection = models.ForeignKey(Collection)

class Price(models.Model):
    price = models.FloatField()
    products = models.ForeignKey(Products, on_delete=models.CASCADE)

and i have data related to models as:

Collection:
+----+--------------------+
| id | date_of_collection |
+----+--------------------+
|  1 | 2019-01-17         |
|  2 | 2019-01-30         |
|  3 | 2019-02-01         |
|  4 | 2019-02-02         |
+----+--------------------+

Products:

 +----+--------------------------------+
 | id | name           | collection    |
 +----+--------------------------------+
 |  1 | product 1      | 3             |
 |  2 | product 2      | 1             |
 |  3 | product 3      | 1             |
 |  4 | product 4      | 4             |
 +----+--------------------------------+

Price:

| id     | price            | product               |
+--------+------------------+-----------------------+
| 1      | 10.00            | 1                     |
| 2      | 20.00            | 1                     |
| 3      | 12.00            | 3                     |
+--------+------------------+-----------------------+

here I have price related to only 1 and 3 product so I only want those products based on queryset that i only want to filter based on specific date_of_collection.

I have tried the following queryset: 

collection_month = Collection.objects.filter(date_of_collection__month=2)
product = Product.objects.filter(collection_id__in=collection_month).exclude(price_set__price=None)

is it the way I do or some next way.. it gives sometimes bad result. How do I do it.

Dipesh Bajgain
  • 801
  • 1
  • 10
  • 26

1 Answers1

2

You are pretty close.
You shouldn't compare collection_id with actual collection items - you can just pass collection__in=collection_month.
You can directly exclude products without price with price__isnull=True

This query will use subquery (WHERE):

collection_month = Collection.objects.filter(date_of_collection__month=2)
products = Product.objects.filter(collection__in=collection_month).exclude(price__isnull=True)

This query will use INNER JOIN which is rumored to be faster:

products = Product.objects.filter(collection__date_of_collection__month=2).exclude(price__isnull=True)
Gasanov
  • 2,839
  • 1
  • 9
  • 21
  • Thanks for your reply, my database has some condition that really requires `collection__in` to check for data. I hope `.exclude(price__isnull=True` won't work we should use `price_set__price__isnull=True` else we get `django.core.exceptions.FieldError` – Dipesh Bajgain May 22 '19 at 11:18
  • @dipbazz did you actually check results? It's working just fine on my end. I can send you my unit test that shows it's working. – Gasanov May 22 '19 at 11:23
  • It is also working for me but the thing is I have to use `related_name__fields` to look up its related objects. I just cannot access the fields of the child objects directly from parent objects. – Dipesh Bajgain May 22 '19 at 11:39
  • @dipbazz Can you bring example of in what query you can't access fields of the child objects from parent objects? What you want to fetch? – Gasanov May 22 '19 at 12:00
  • As I have set my `related_name` in foreignkey fields of price models my `related_query_name` will be defaults to the value of `related_name` as stated in [documentaion of django](https://docs.djangoproject.com/en/2.2/ref/models/fields/#django.db.models.ForeignKey.related_query_name) So, I have to use related name instead of model name in lookups. – Dipesh Bajgain May 22 '19 at 14:52