How to join attributes of a reverse ManyToManyField in Django ORM

Suppose that these are my models:

class Product(models.Model):
     sku = models.CharField(unique=True) # Something like RXT00887


class Bill(models.Model):
     customer = models.ForeignKey(Customer)
     products = models.ManyToManyField(Product)
     date = models.DateTimeField()

Each Product is related to only one Bill or none at all.

I am working on a feature which requires me to search through the Products that were bought by a given customer. I am achieving this through:

bills = Bill.objects.filter(customer=customer)
results = Product.objects.filter(bill__in=bills, sku__icontains=search_str)

However, I also need to have some attributes of Bill of each product in the results. For example, I need the bill id and the bill date. So each row in the result should contain all attributes of the product along with two additional attributes: bill_id and bill_date. Is it possible to achieve this is Django by using joins? If so, how?

Each Product is related to only one Bill or none at all.

If that is the case, the modeling is wrong. Then the Product needs a ForeignKey to Bill, like:

class Product(models.Model):
    sku = models.CharField(unique=True)  # Something like RXT00887
    bill = models.ForeignKey(
        'Bill', related_name='products', on_delete=models.PROTECT
    )


class Bill(models.Model):
    customer = models.ForeignKey(Customer)
    # products = models.ManyToManyField(Product)
    date = models.DateTimeField()

Then you can select the Bill along with the Products:

results = Product.objects.filter(
    bill__customer=customer, sku__icontains=search_str
).select_related('bill')

You can thus for each product access my_product.bill which is then a Bill object with the required data.

Back to Top