mk2 mk2 -4 years ago 74
MySQL Question

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!

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download