CODEWITHSUNDEEP
djangodjango-modelsdjango-postgresql
davenenglish
davenenglish

Reputation: 108

Django Arrayfiled: Annotate queryset with the first item in the arryafield

I have a model that can be represented by something like this.

class BackPack(models.Model):
    person = models.ForeignKey(Person, db_index=True,
                             related_name='back_packs', on_delete=models.CASCADE)
    candy = ArrayField(models.CharField(max_length=203, null=True))

I want to build a queryset that is all back packs associated with one person and then annotated with the first item in the candy arrayfield. I tried the following;

first_candy = BackPack.objects.filter(person__id=200)\
                              .annotate(first_candy=F('candy__0'))

first_candy = BackPack.objects.filter(person__id=200)\
                              .annotate(first_candy=ExpressionWrapper(F('candy__0'),
                                                 output_field=CharField()))

The output for first_candy includes every item in the arrayfield not just the first one.

Any help for the correct way of doing this is much appreciated.

Upvotes: 0

Views: 1064

Answers (1)

Ulladimil
Ulladimil

Reputation: 36

Try this:

from django.db.models.expressions import RawSQL

BackPack.objects.filter(person__id=200).annotate(first_candy=RawSQL('candy[1]', ()))

Postgres arrays are 1-based by default

Upvotes: 2

Related Questions