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

Postgresql – Django RawSQL annotate field

How can i annotate field with RawSQL?

    sql_query = """
            SELECT 
                c.id, 
                COALESCE(SUM((cm.temp_min + cm.temp_max) / 2 - %s), 0) AS gdd
            FROM 
                agriculture_commune c
            LEFT JOIN 
                agriculture_communemeteo cm ON c.id = cm.commune_id
            WHERE 
                cm.date BETWEEN %s AND %s
            GROUP BY 
                c.id
        """
    communes =  communes.raw(sql_query, [TBASE, start_date, end_date])

it working , if i try do like this

communes.annotate(gdd=RawSQL(sql_query, [TBASE, start_date, end_date]))

i got error "subquery must return only one columnnLINE 1: …mmune"

Tags:

2

Answers


  1. 0

    As the error said, when using RawSQL to annotate a field the SQL query you provide should return only a single value per row because it’s being used as part of a larger query to generate the annotated queryset.

    So you need to use a query like this which returns only a single value:

    SELECT 
    COALESCE(SUM((cm.temp_min + cm.temp_max) / 2 - %s), 0) AS gdd
FROM 
    agriculture_communemeteo cm
WHERE 
    cm.commune_id = agriculture_commune.id AND
    cm.date BETWEEN %s AND %s

    and then annotate just as you did:

    communes.annotate(gdd=RawSQL(sql_query, [TBASE, start_date, end_date]))
    Login or Signup to reply.
  2. 0

    There is no need to use a raw query this can be determined with:

    from django.db.models import F, Value
from django.db.models.functions import Coalesce

Commune.objects.filter(communemeteo__date__range=(start_date, end_date)).annotate(
    gdd=Coalesce(
        (F('communemeteo__temp_min') + F('communemeteo__temp_max')) / 2
        - Value(TBASE),
        0,
    )
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search