CODEWITHSUNDEEP
djangodjango-queryset
Thierry J
Thierry J

Reputation: 2189

Filter out objects from queryset only if another object is in queryset

Considering a very simple model as such:

class Thing(models.Model):
    attribute = models.IntegerField(choices=[1,2,3])

I would like all Things, except those with attribute=2.
But I do want these with attribute=2 if there is no Things with attribute other than 2 in the result set.

How can I get this result with a single query?

[Edit]
Actually I only need the first Thing that has an attribute different than 2, or the first thing with an attribute of 2 if this is the only kind of Thing available.

I have been able to achieve that with a function as

def get_thing():
    things_per_attribute = [
        Thing.objects.filter(attribute=value)
        for value in [1, 3, 2]
    ]
    for thing in things_per_attribute:
        if thing.count() > 0:
            return thing[0]
    return None

I am still interested to see if anyone can propose a 1-query-solution.

Upvotes: 1

Views: 284

Answers (1)

Vladimir Danilov
Vladimir Danilov

Reputation: 2398

One query:

from django.db.models import BooleanField, Case, When

Thing.objects.annotate(
    attribute_is_two=Case(
        When(attribute=2, then=True),
        default=False,
        output_field=BooleanField()
    ),
).order_by('-attribute_is_two').first()

Two queries:

def get_thing():
    thing = Things.objects.exclude(attribute=2).first()
    if thing:
         return thing
    return Thing.objects.first()

Upvotes: 1

Related Questions