Django Filter Queryset _In for *Every* Item in List

Django filter queryset __in for *every* item in list

Summary:

One option is, as suggested by jpic and sgallen in the comments, to add .filter() for each category. Each additional filter adds more joins, which should not be a problem for small set of categories.

There is the aggregation approach. This query would be shorter and perhaps quicker for a large set of categories.

You also have the option of using custom queries.

---

Some examples

Test setup:

class Photo(models.Model):
    tags = models.ManyToManyField('Tag')

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

    def __unicode__(self):
        return self.name

In [2]: t1 = Tag.objects.create(name='holiday')
In [3]: t2 = Tag.objects.create(name='summer')
In [4]: p = Photo.objects.create()
In [5]: p.tags.add(t1)
In [6]: p.tags.add(t2)
In [7]: p.tags.all()
Out[7]: [<Tag: holiday>, <Tag: summer>]

Using chained filters approach:

In [8]: Photo.objects.filter(tags=t1).filter(tags=t2)
Out[8]: [<Photo: Photo object>]

Resulting query:

In [17]: print Photo.objects.filter(tags=t1).filter(tags=t2).query
SELECT "test_photo"."id"
FROM "test_photo"
INNER JOIN "test_photo_tags" ON ("test_photo"."id" = "test_photo_tags"."photo_id")
INNER JOIN "test_photo_tags" T4 ON ("test_photo"."id" = T4."photo_id")
WHERE ("test_photo_tags"."tag_id" = 3  AND T4."tag_id" = 4 )

Note that each filter adds more JOINS to the query.

Using annotation approach:

In [29]: from django.db.models import Count
In [30]: Photo.objects.filter(tags__in=[t1, t2]).annotate(num_tags=Count('tags')).filter(num_tags=2)
Out[30]: [<Photo: Photo object>]

Resulting query:

In [32]: print Photo.objects.filter(tags__in=[t1, t2]).annotate(num_tags=Count('tags')).filter(num_tags=2).query
SELECT "test_photo"."id", COUNT("test_photo_tags"."tag_id") AS "num_tags"
FROM "test_photo"
LEFT OUTER JOIN "test_photo_tags" ON ("test_photo"."id" = "test_photo_tags"."photo_id")
WHERE ("test_photo_tags"."tag_id" IN (3, 4))
GROUP BY "test_photo"."id", "test_photo"."id"
HAVING COUNT("test_photo_tags"."tag_id") = 2

ANDed Q objects would not work:

In [9]: from django.db.models import Q
In [10]: Photo.objects.filter(Q(tags__name='holiday') & Q(tags__name='summer'))
Out[10]: []
In [11]: from operator import and_
In [12]: Photo.objects.filter(reduce(and_, [Q(tags__name='holiday'), Q(tags__name='summer')]))
Out[12]: []

Resulting query:

In [25]: print Photo.objects.filter(Q(tags__name='holiday') & Q(tags__name='summer')).query
SELECT "test_photo"."id"
FROM "test_photo"
INNER JOIN "test_photo_tags" ON ("test_photo"."id" = "test_photo_tags"."photo_id")
INNER JOIN "test_tag" ON ("test_photo_tags"."tag_id" = "test_tag"."id")
WHERE ("test_tag"."name" = holiday  AND "test_tag"."name" = summer )

Django filter queryset __in for *every* item in list (2.0)

I found it! Can't avoid double query but it works like a charm. Here's the solution:

• first, filter on Ingredients that are part of a recipe, for each recipe (= group by), count the total of ingredients found
• then for all the existing recipes, if the total of ingredient == the total of ingredients founds before, then it's ok, keep it.

It feels like using a sledgehammer to crack a nut (even though the first query filters and eliminates a lot of recipes), but it works, if you have a better solution I'm your man!

recipes = Recipe.objects \
    .annotate(found=Count('*'))\
    .filter(ingredients__in=ingredient_ids)
for recipe in recipes:
    a = Recipe.objects.annotate(total=Count('ingredients')).filter(
        pk=recipe.pk, total=recipe.found)
    print("Recipe found:", str(a))

And for example, if the ids of the ingredients are [1, 2, 3, 4, 5] you'll get those two queries:

SELECT "app_recipe"."id", "app_recipe"."label", "app_recipe"."description",
    COUNT(*) AS "found" FROM "app_recipe"
INNER JOIN "app_recipe_ingredients"
ON ("app_recipe"."id" = "app_recipe_ingredients"."recipe_id")
WHERE "app_recipe_ingredients"."ingredientunit_id" IN (1, 2, 3, 4, 5)
GROUP BY "app_recipe"."id", "app_recipe"."label", "app_recipe"."description";

And the second loop will make queries based on the recipes found like this:

SELECT "app_recipe"."id", "app_recipe"."label", "app_recipe"."description",
    COUNT("app_recipe_ingredients"."ingredientunit_id") AS "total" 
FROM "app_recipe" 
LEFT OUTER JOIN "app_recipe_ingredients" 
ON ("app_recipe"."id" = "app_recipe_ingredients"."recipe_id") 
WHERE "app_recipe"."id" = 1 
GROUP BY "app_recipe"."id", "app_recipe"."label", "app_recipe"."description"
HAVING COUNT("app_recipe_ingredients"."ingredientunit_id") = 5;

Django filter queryset __in for items only in list

You can check if the number of Tags that satisfy the predicate is the same as the total number of tags:

from django.db.models import Count, Q

Post.objects.annotate(
    ntags=Count('tags')
).filter(
    ntags=Count('tags', filter=Q(tags__name__in=['a', 'b'])),
    ntags__gt=0
)

The first filter thus checks if the total number of related tags is the same as the number of tags with as name 'a' or 'b'. If the number is different, then we know that there is a tag with a different name.

The second filter checks if the number of tags is greater than zero, to exclude matches with no tags at all.

How do I do an OR filter in a Django query?

There is Q objects that allow to complex lookups. Example:

from django.db.models import Q

Item.objects.filter(Q(creator=owner) | Q(moderated=False))

Django - How to query a list inside of a filter method

We can use a subquery to obtain the last user, and then filter accordingly:

from django.db.models import OuterRef, Subquery, Q

Batch.objects.annotate(
    last_user=Subquery(
        BatchLogComment.objects.filter(
            batch=OuterRef('pk')
        ).order_by('-created_at').values('user')[:1]
    )
).filter(
    ~Q(last_user=self.request.user),
    comments__user=self.request.user
)

Django ORM values_list with '__in' filter performance

These should be totally equivalent. Underneath the hood Django will optimize both of these to a subselect query in SQL. See the QuerySet API reference on in:

This queryset will be evaluated as subselect statement:

SELECT ... WHERE consumer.id IN (SELECT id FROM ... WHERE _ IN _)

However you can force a lookup based on passing in explicit values for the primary keys by calling list on your values_list, like so:

providers_ids = list(Provider.objects.filter(age__gt=10).values_list('id', flat=True))
consumers = Consumer.objects.filter(consumer__in=providers_ids)

This could be more performant in some cases, for example, when you have few providers, but it will be totally dependent on what your data is like and what database you're using. See the "Performance Considerations" note in the link above.

How to filter sentences from an list of words in array in Django

I solved this problem by using Q

def get_queryset(self):
    book_pk = self.kwargs['pk']
    book = Video.objects.get(pk=book_pk)
    lst = book.title
    split = lst.split()

    return Book.objects.filter(Q(title__icontains=split[0]) & Q(title__icontains=split[1]))

this get the first and secon word of title and try to get a books with titles that contains this words

django icontains with __in lookup

You can create querysets with the Q constructor and combine them with the | operator to get their union:

from django.db.models import Q

def companies_matching(merchants):
    """
    Return a queryset for companies whose names contain case-insensitive
    matches for any of the `merchants`.
    """
    q = Q()
    for merchant in merchants:
        q |= Q(name__icontains = merchant)
    return Companies.objects.filter(q)

(And similarly with iexact instead of icontains.)

---

Related Topics