Django get data by one table with filtered set of related objects

Question

My intent is to get all Offers based on site from model OfferSite with a filter on OfferItem model for today's date.

When I select all without filter on the second model, I am getting desired output (only 2 sites with offeritem_set),

In [23]: OfferSite.objects.all().select_related("offeritem")
Out[23]: [<OfferSite: OfferSite object>, <OfferSite: OfferSite object>]

But when I try to filter on the second model I get many objects, I think it returns OfferSite object for every OfferItem. I was expecting only two OfferSite objects with offeritem_set to fetch all filtered OfferItems

``
In [24]: OfferSite.objects.all().select_related("offeritem").filter(offeritem__offer_date=tod)
Out[24]: [<OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, <OfferSite: OfferSite object>, '...(remaining elements truncated)...']

Is there a Django ORM way of getting the desired output?

My models

class OfferSite(models.Model):
    name = models.CharField(max_length=30)
    domain_url = models.URLField()


class OfferItem(models.Model):
    title = models.CharField(max_length=255)
    link = models.CharField(max_length=750)
    image = ImageField()
    price_after_discount = models.CharField(max_length=100, blank=True, null=True)
    price_before_discount = models.CharField(max_length=100, blank=True, null=True)
    discount = models.CharField(max_length=100, blank=True, null=True)
    offer_date = models.DateField(auto_now_add=True, default=datetime.date.today())
    offer_from = models.DateTimeField(blank=True, null=True)
    offer_to = models.DateTimeField(blank=True, null=True)
    single_item = models.BooleanField(default=True)
    site = models.ForeignKey(OfferSite)
    archived = models.BooleanField(default=False)
    likes = models.IntegerField(max_length=4, default=0)
    unlikes = models.IntegerField(max_length=4, default=0)
    abusive = models.IntegerField(max_length=3, default=0)

score 4 · Accepted Answer · edited May 23 '17 at 12:32

Just in case others are looking for a solution. This question answers it Why does django's prefetch_related() only work with all() and not filter()?. The title of that question wasn't reflecting the issue so I missed it.

The solution in my case is,

OfferSite.objects.all().prefetch_related(Prefetch("offeritem_set", queryset=OfferItem.objects.filter(offer_date=tod), to_attr="offers"))

This applies only to Django 1.7 & above.

Django get data by one table with filtered set of related objects

My models

1 Answers1