Re: need not_equal and not_in query lookup types for a natural use case that can't easily be implemented with existing queries
BTW, if I understand your problem correctly you might be able to do this
using a subquery:
non_special_items = Item.objects.exclude(id__in=special_items)
special_only_bundles = Bundle.objects.exclude(items__in=non_special_items)
On Thursday, May 19, 2016 at 11:19:42 PM UTC+10, David Xiao wrote:
>
> Hi folks,
>
> Django is missing a not_equal lookup type for database queries. I know
> this has been requested before and that the usual response is to use exclude
> or ~Q. While that works for a lot of use cases, I have a natural use
> case that I haven't seen discussed and that seems to escape easy
> implementation with the existing queryset implementation. (I already
> posted to the django-users group and nobody was able to give a satisfactory
> answer. If there is an easy answer, one using a single query, please let
> me know.)
>
> Example: suppose we have the following models:
>
> class Bundle(Model)
> items = ManyToManyField("Item")
>
> class Item(Model)
> pass
>
> (So one item can belong to many Bundles and one Bundle can have many
> Items.)
>
> The query: I want to select all Bundles whose items are contained in an
> array special_items. (Think of special_items as being relatively small
> and the set of all possible items as large.)
>
> Example: Suppose the database contains Items item1, item2, item3 (possibly
> among many other items) and that special_items = [item1, item2]. Suppose
> also the following Bundles exist in the database:
> 1. Bundle containing item1
> 2. Bundle containing item2
> 3. Bundle containing item1, item2
> 4. Bundle containing item2, item3
> 5. Bundle containing item1, item2, item3
>
> So the query should return bundles 1, 2, 3 but exclude bundles 4, 5
> because they contain item3, which is not a special item.
>
> Queries that don't work:
> - Bundle.exclude(items__in=special_items) which returns no bundles
> - Bundle.filter(items__in=special_items) which returns bundles 1,2,3,4,5
> - Bundle.exclude(~Q(items__in=special_items)) which returns 1,2,3,4,5
>
> One work-around that does work is
> Bundle.exclude(items__in=not_special_items) where not_special_items contains
> all possible items that are not in special_items. But clearly this is
> impractical/unusable if the set of possible items is infinite or very large.
>
> On the other hand, if we had a not_equal (or even better, a not_in)
> operator, we could do the query as follows:
> Bundle.exclude(items__not_in=special_items)
> Bundle.filter(~Q(items__not_equal=special_items[0]) &
> ~Q(items__not_equal=special_items[1]) & ...)
> (The latter should be OK because in typical use cases special_items is a
> small set.)
>
> Note: I did try to implement this as a custom lookup, but I guess because
> it's spanning a relationship you can't use the regular custom lookup API.
> If there is an easy way to add this as a custom lookup I'd appreciate some
> guidance.
>
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/64749196-f6d6-4cab-94ed-fb19e25638da%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: need not_equal and not_in query lookup types for a natural use case that can't easily be implemented with existing queries
> If there is an easy answer, one using a single query, please let me know.)
Can you post what your desired SQL would look like?
IIRC, foo.exclude(id__in=[bar, baz]) already adds "NOT IN" to the SQL query
(but I may be wrong), i.e "SELECT foo WHERE id NOT IN (bar, baz)"
So what do you propose foo.exclude(id__not_in=[bar, baz]) would look like
as a single SQL query?
On Thursday, May 19, 2016 at 11:19:42 PM UTC+10, David Xiao wrote:
>
> Hi folks,
>
> Django is missing a not_equal lookup type for database queries. I know
> this has been requested before and that the usual response is to use exclude
> or ~Q. While that works for a lot of use cases, I have a natural use
> case that I haven't seen discussed and that seems to escape easy
> implementation with the existing queryset implementation. (I already
> posted to the django-users group and nobody was able to give a satisfactory
> answer. If there is an easy answer, one using a single query, please let
> me know.)
>
> Example: suppose we have the following models:
>
> class Bundle(Model)
> items = ManyToManyField("Item")
>
> class Item(Model)
> pass
>
> (So one item can belong to many Bundles and one Bundle can have many
> Items.)
>
> The query: I want to select all Bundles whose items are contained in an
> array special_items. (Think of special_items as being relatively small
> and the set of all possible items as large.)
>
> Example: Suppose the database contains Items item1, item2, item3 (possibly
> among many other items) and that special_items = [item1, item2]. Suppose
> also the following Bundles exist in the database:
> 1. Bundle containing item1
> 2. Bundle containing item2
> 3. Bundle containing item1, item2
> 4. Bundle containing item2, item3
> 5. Bundle containing item1, item2, item3
>
> So the query should return bundles 1, 2, 3 but exclude bundles 4, 5
> because they contain item3, which is not a special item.
>
> Queries that don't work:
> - Bundle.exclude(items__in=special_items) which returns no bundles
> - Bundle.filter(items__in=special_items) which returns bundles 1,2,3,4,5
> - Bundle.exclude(~Q(items__in=special_items)) which returns 1,2,3,4,5
>
> One work-around that does work is
> Bundle.exclude(items__in=not_special_items) where not_special_items contains
> all possible items that are not in special_items. But clearly this is
> impractical/unusable if the set of possible items is infinite or very large.
>
> On the other hand, if we had a not_equal (or even better, a not_in)
> operator, we could do the query as follows:
> Bundle.exclude(items__not_in=special_items)
> Bundle.filter(~Q(items__not_equal=special_items[0]) &
> ~Q(items__not_equal=special_items[1]) & ...)
> (The latter should be OK because in typical use cases special_items is a
> small set.)
>
> Note: I did try to implement this as a custom lookup, but I guess because
> it's spanning a relationship you can't use the regular custom lookup API.
> If there is an easy way to add this as a custom lookup I'd appreciate some
> guidance.
>
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/abca5d8c-1ec9-4267-9c09-d20e51bbc750%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
need not_equal and not_in query lookup types for a natural use case that can't easily be implemented with existing queries
Hi folks,
Django is missing a not_equal lookup type for database queries. I know
this has been requested before and that the usual response is to use exclude
or ~Q. While that works for a lot of use cases, I have a natural use case
that I haven't seen discussed and that seems to escape easy implementation
with the existing queryset implementation. (I already posted to the
django-users group and nobody was able to give a satisfactory answer. If
there is an easy answer, one using a single query, please let me know.)
Example: suppose we have the following models:
class Bundle(Model)
items = ManyToManyField("Item")
class Item(Model)
pass
(So one item can belong to many Bundles and one Bundle can have many Items.)
The query: I want to select all Bundles whose items are contained in an
array special_items. (Think of special_items as being relatively small and
the set of all possible items as large.)
Example: Suppose the database contains Items item1, item2, item3 (possibly
among many other items) and that special_items = [item1, item2]. Suppose
also the following Bundles exist in the database:
1. Bundle containing item1
2. Bundle containing item2
3. Bundle containing item1, item2
4. Bundle containing item2, item3
5. Bundle containing item1, item2, item3
So the query should return bundles 1, 2, 3 but exclude bundles 4, 5 because
they contain item3, which is not a special item.
Queries that don't work:
- Bundle.exclude(items__in=special_items) which returns no bundles
- Bundle.filter(items__in=special_items) which returns bundles 1,2,3,4,5
- Bundle.exclude(~Q(items__in=special_items)) which returns 1,2,3,4,5
One work-around that does work is
Bundle.exclude(items__in=not_special_items) where not_special_items contains
all possible items that are not in special_items. But clearly this is
impractical/unusable if the set of possible items is infinite or very large.
On the other hand, if we had a not_equal (or even better, a not_in)
operator, we could do the query as follows:
Bundle.exclude(items__not_in=special_items)
Bundle.filter(~Q(items__not_equal=special_items[0]) &
~Q(items__not_equal=special_items[1]) & ...)
(The latter should be OK because in typical use cases special_items is a
small set.)
Note: I did try to implement this as a custom lookup, but I guess because
it's spanning a relationship you can't use the regular custom lookup API.
If there is an easy way to add this as a custom lookup I'd appreciate some
guidance.
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/24bfac80-c102-404a-9b91-c35059545742%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
