Как преобразовать SQL в Django ORM

Question

Как можно преобразовать этот SQL на Django

select
    carservice_client."clientCity",
    "clientName",
    average_price_sity,
    sum(c.price) sum_price_client
from carservice_client
JOIN carservice_serviceclient cs on carservice_client.id = cs.client_id
JOIN carservice_service c on c.id = cs.service_id
INNER JOIN (select
    carservice_client."clientCity",
    avg(c.price) + ((avg(c.price) * 20) / 100)  average_price_sity
    from carservice_client
    JOIN carservice_serviceclient cs on carservice_client.id = cs.client_id
    JOIN carservice_service c on c.id = cs.service_id
    group by "clientCity") sity_price on sity_price."clientCity" = carservice_client."clientCity"
group by carservice_client."clientCity", "clientName", average_price_sity
having  sum(c.price) > average_price_sity

Answer 1

Вот решение как обработать больше одной строки с Subquery

 CarserviceClient.objects\
            .values("clientname", "clientcity")\
            .annotate(sum_price=Sum("carserviceserviceclient__service__price"),
                      average_price_city=Subquery(CarserviceClient.objects
                                                  .values("clientcity")
                                                  .annotate(average_price=Avg("carserviceserviceclient__service__price") +
                                                                          ((Avg("carserviceserviceclient__service__price") * 20) / 100))
                                                  .filter(clientcity=OuterRef("clientcity"))
                                                  .values("average_price")[:1]
                                                  )
                      )\
            .filter(sum_price__gt=F("average_price_city"))

БЛОГ НА HUSL

Как преобразовать SQL в Django ORM

Ответы (1 шт):