CODEWITHSUNDEEP
pythonmysqldjango
renno
renno

Reputation: 2827

Make Join in Django queryset to filter it

I have two querysets and I'd like to make a join, but I'm not sure how to proceed. I want to filter data based upon two annotation created on each queryset

Models

class Recipe(models.Model):
  name = models.CharField(max_length=50)
  steps = models.ManyToManyField(StepRecipe)

class StepRecipe(models.Model):
  ingredients = models.ManyToManyField(RecipeIngredient)

class RecipeIngredient(models.Model):
  ingredient = models.ForeignKey(Ingredient)

class Ingredient(models.Model):
  name = models.CharField(max_length=50)

Queryset in steps

# Example:
# Main queryset
q = Recipe.objects.all()
ingredients = ['salt','oil','flour','tomato']
# This result in all the ingredients necessary
q1 = q.annotate(total=Count('steps__ingredients__ingredient', distinct=True))
# q1 = [{id:1, name: 'salad', total: 5}, {id:2, name: 'pasta', total: 4}]
# This result in the available ingredients. 
# First I filtered the ingredients I have, them I counted them
q2 = q.filter(steps__ingredients__ingredient__name__in=ingredients)
      .annotate(available=Count('steps__ingredients__ingredient',distinct=True))
# q2 = [{id:1, name: 'salad', available: 3}, {id:2, name: 'pasta', available: 4}]

I'd like to join both results and filter by .filter(total=F('sum')) at the end. After filtering this would result on showing only the object of ID 2 because total = 4 and available = 4.

I know I have available the | operator. I'd have to do something in this line:

result = q1 | q2

But whenever I do this, one of the annotations disappear (the last one, in this case q2 annotation which is available). Any ideas? I don't want to iterate over them if the DB can do that.

Strangely enough, there were times that when I put everything in one line, it would give me the expected results often times. I have the impression this can be a bug. I think it cached somehow the result...

This is what I did and it resulted correctly a few times:

queryset = Recipe.objects.all()
        .annotate(total=Count('steps__ingredients', distinct=True))
        .filter(steps__ingredients__ingredient__name__in=ingredients)
        .annotate(sum=Count('steps__ingredients', distinct=True))
        .filter(total=F('sum'))

I'm using MySQL 5.6 and Django 1.11

I tried to use .intersection() which is new in Django 1.11 and it did not work either (my DB doesn't support apparently). I thought about using .union(), but the same thing that happened to the operator | happened to this function: one annotation disappeared. Although, one disappeared I thought I could see whatever appeared twice and filter by that. But... again, another error! when I tried to annotate I got IndexError: list index out of range

If there is a better idea (not iterate) or a good way to fix this which doesn't involve raw queries, I'd appreciate. Thanks!

Upvotes: 1

Views: 859

Answers (1)

renno
renno

Reputation: 2827

This is a bug from Django and it's being fixed https://code.djangoproject.com/ticket/28297

Upvotes: 1

Related Questions