Question 310558 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is there a way to copy annotations from one Django queryset to another?

I am currently trying to overcome the challenge of ordering a queryset in Django with foreign key attributes.

As you may know this can have unintended results with duplicate entries. The other option is to specify an ordering on the same queryset but to avoid duplicates but this leads to SELECT DISTINCT ON expressions must match initial ORDER BY expressions

So I resorted to creating another queryset to filter the id’s in an already annotated queryset so i can get a fresh queryset with which can be ordered by what is needed in the end. I also tried to copy the annotations present on one queryset to another using the queryset.query.annotations ValueDict but it failed.

Here is the scenario:

Models

class Home(models.Model):
  street=models.CharField(...)
  city=models.CharField(...)
  
class Person(models.Model):
  name=models.CharField(...)
  home=models.ForeignKey(Home,...)

Objective

  • Get a of Person queryset ordered by the street, city combination in one string (Concatenation).

Attempt

queryset=Person.objects.annotate(addr=Concat('home__street', Value(' '), 'home__city'))

queryset.filter(...).order_by('addr').distinct('pk')
# Failed with an error

Attempt 2 (filter by pks on original )

Person.objects.filter(pk__in=
queryset.filter(...).values('pk')
  .order_by('pk').distinct('pk')
).order_by('addr')

# This fails because *addr* is not field on the new queryset. 
# I could define a custom *BaseModel* buy there are so many models with other complex relations.
Here are my attempts to solve my problem:

Copying annotations from one queryset to another

annotations=queryset.query.annotations

Person.objects.annotate(**annotations)
  .filter(
       pk__in=queryset.order_by('pk')
         .distinct('pk').values('pk')
  ).order_by('addr')

# Failed because the annotations seemed to have been tightly coupled to the original queryset after their declaration
Tags:

2

Answers


  1. 0

    👉1st method

    Advantages :-

    1. Only one query
    2. simplicity

    Do it like below.

    class Person(models.Model):
  name=models.CharField(...)
  home=models.ForeignKey(Home,...)
 

  def adress(self):
    adress = self.street + self.city
    return adress 


    

queryset=Person.objects.filter(...).order_by('adress').distinct('pk')

Or

queryset=Person.objects.filter(adress='your_search_adress').order_by('adress').distinct('pk')

    ///////////////////////////////////////////////

    For
    Copying annotations from one queryset to another
    (you can copy annotates, but
    Annotated values become just the same as object properties after being annotated(that’s what the annotation mean).
    So,

    annotations=queryset.query.annotations

    Is not valid.
    So, what you can do is,

    👉2nd method

    Disadvantages:-

    1. use of loops, comprehensions
    2. low simplicity

    ** Use,

    1. for loop
    2. python comprehensions.
    Login or Signup to reply.
  2. 0

    Get a of Person queryset ordered by the street, city combination in one string (Coalation)

    I think you mean concatenation or coalescing, not collation. Coalate is not an ORM expression, but the syntax looks like you wanted Concat. Try something like:

    from django.db.models import CharField, Value
from django.db.models.functions import Concat

queryset = (
    Person.objects
    .annotate(
        addr=Concat(
            'street', Value(' '), 'city', 
            output_field=CharField()
        )
    )
    .order_by('addr')
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search