CODEWITHSUNDEEP
djangodjango-modelsdjango-views
gerpaick
gerpaick

Reputation: 799

Django - ManyToMany - how to filter on parent's ManyToMany object

I have problems with writing a filter on my queryset models:

class Order(models.Model):
  items = models.ManyToManyField(OrderLine)
  store = models.ForeignKey(Store, blank=True, null=True, on_delete=models.SET_NULL)

class OrderLine(models.Model):
  orderItemId = models.IntegerField(blank=False, null=False, unique=True)
  quantity = models.IntegerField(blank=True, null=True)

class Store(models.Model):
  storeName = models.CharField(max_length=200, blank=True, null=True)
  access_from_account = models.ManyToManyField(EDUser, blank=True)

I have my queryset of EDUser "accounts" Now I want to select OrderLines that belongs to Order that are from Store that is visible by an account in access_from _account.

So, for example:

store_name_1 has access_from_account user_1, user_2

store_name_2 has access_from_account user_1

There at Orders that belongs to store_name_1. Those Orders have many OrderLines. I would like to select order_lines that should be accessible by user_1.

Can I do it by: acounts is queryset of ['user_1']

lines = OrderLine.objects.filter(order__store__access_from_account__in=accounts)? I tried that, but I get some strange values...

Any suggestion of how to do it properly? I would like to avoid making lists and iterate over them.

Thanks in advance :)

Upvotes: 1

Views: 3069

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477170

The query

lines = OrderLine.objects.filter(
    order__store__access_from_account__in=accounts
)

will work propery. The only problem is that if accounts contains two users, like user1 and user2, and both accounts have access to a store, it will duplicate the lines. You will thus retrieve OrderLines for user1 and for user2. This is due to the LEFT OUTER JOIN on the junction table in between. You can solve this by making use of .distinct() [Django-doc]:

lines = OrderLine.objects.filter(
    order__store__access_from_account__in=accounts
).distinct()

If you are working with a single EDUser, then you do not need the __in lookup:

# for a single user

lines = OrderLine.objects.filter(
    order__store__access_from_account=myuser
)

Upvotes: 2

Related Questions