Let's say for example I have these models:

class Person(models.Model):
    name = models.CharField(max_length=50, blank=True, null=True, verbose_name=_('name'))


class Keyword(models.Model):
    value = models.CharField(max_length=50, blank=True, null=True, verbose_name=_('name'))
    person = models.ForeignKey('Keyword', on_delete=models.CASCADE)

I'd like to query on the Person model, where we filter on ONLY the first keyword linked to that person.

The keyword list is always ordered.

So for example:

Person John doe has 3 keyword (Programming, Travel, Family)

I'd like to do something like this (if this is possible)

Person.objects.filter(keyword_"first"="Programming") 

CodePudding user response：

You can annotate with .alias(…) ^[Django-doc] with a Subquery expression ^[Django-doc], and then filter:

from django.db.models import OuterRef, Subquery

class Person.objects.alias(
    first_keyword=Subquery(
        Keyword.objects.filter(
            person=OuterRef('pk')
        ).values('value')[:1]
    )
).filter(
    first_keyword='Programming'
)

But models are by default unordered: that means that each time a different keyword can be the first. Only if you have a column and an ordering option ^[Django-doc], or an .order_by(…) clause ^[Django-doc] in the subquery, it is guaranteed that a certain item will be the first keyword.

You can make the filtering optional with a field only_first that will filter on the first item if True and on all items if False:

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

class Person.objects.alias(
    first_keyword=Subquery(
        Keyword.objects.filter(
            person=OuterRef('pk')
        ).values('value')[:1]
    )
).filter(
    Q(first_keyword='Programming') |
    Q(only_first=False, keyword__value='Programming')
)