Django 1.11 Subquery agregatini tushuntirish

Bu mening qoniqishimga asoslangan xususiyat bo'lib, hozirda skeletim va tez qon ketishim. Mavjud so'rovlar varag'iga pastki so'rov-agregatni izohlashni xohlayman. Buni 1.11dan oldin bajarish SQL-ni yoki ma'lumotlar bazasini ishga tushirishni anglatardi. Buning uchun hujjatlar va undan misol:

from django.db.models import OuterRef, Subquery, Sum
comments = Comment.objects.filter(post=OuterRef('pk')).values('post')
total_comments = comments.annotate(total=Sum('length')).values('total')
Post.objects.filter(length__gt=Subquery(total_comments))

Ular jamlanishda annotating , ular menga g'alati tuyuladi, lekin nima bo'lishidan qat'iy nazar.

Men bu bilan kurashyapman, shuning uchun uni hozirgi eng oddiy real dunyo namunasiga qaynataman. Ko'p Space ni o'z ichiga olgan Carpark ni bor. Kitob → Muallif dan foydalaning, agar u sizni baxtli qiladi, hozir esa - faqatgina Subquery * dan foydalanib tegishli modeldagi hisobni izohlashni xohlayman.

spaces = Space.objects.filter(carpark=OuterRef('pk')).values('carpark')
count_spaces = spaces.annotate(c=Count('*')).values('c')
Carpark.objects.annotate(space_count=Subquery(count_spaces))

Bu menga yaxshi ProgrammingError: bir ifoda sifatida ishlatiladigan subquery tomonidan qaytarilgan bir necha satrni beradi va men boshimda bu xato mukammal bo'ladi. Pastki so'rov, izohli jami bo'sh joylar ro'yxatini qaytarib beradi.

Misol, sehrning qanday turlari bo'lishi mumkinligini va men qo'llashim mumkin bo'lgan son bilan tugashini aytdi. Lekin bu erda emas. Umumiy sintaksis ma'lumotlariga qanday izoh beraman?

Hmm, biror narsa mening so'rovlarim SQL ga qo'shilmoqda ...

Men yangi Carpark/Space modelini qurdim va u ishladi. Shuning uchun keyingi qadam SQLni zaharlaydigan narsalarni ishlab chiqish. Laurentning maslahati bo'yicha men SQLga murojaat qildim va o'zlarining javoblarida chop etgan versiyaga o'xshashroq qilishni istadim. Va bu erda men haqiqiy muammoni topdim:

SELECT "bookings_carpark".*, (SELECT COUNT(U0."id") AS "c"
FROM "bookings_space" U0
WHERE U0."carpark_id" = ("bookings_carpark"."id")
GROUP BY U0."carpark_id", U0."space"
)
AS "space_count" FROM "bookings_carpark";

Men buni ta'kidladim, lekin subquery ning GROUP BY ... U0. "Space" . Har ikkala sababga ko'ra ham, bu qayta tiklanmoqda. Tadqiqotlar davom etmoqda.

Tartibga solish 2: OK, subquery SQLga qarab, ikkinchi guruhni ko'rishim mumkin☹

In [12]: print(Space.objects_standard.filter().values('carpark').annotate(c=Count('*')).values('c').query)
SELECT COUNT(*) AS "c" FROM "bookings_space" GROUP BY "bookings_space"."carpark_id", "bookings_space"."space" ORDER BY "bookings_space"."carpark_id" ASC, "bookings_space"."space" ASC

Edit 3: Okay! Both these models have sort orders. These are being carried through to the subquery. It's these orders that are bloating out my query and breaking it.

I guess this might be a bug in Django but short of removing the Meta-order_by on both these models, is there any way I can unsort a query at querytime?


* Men bu misol uchun faqatgina izoh berishni bilaman. . Buni ishlatish uchun haqiqiy maqsadim ancha murakkab filter-hisoblar, lekin men bu ishni ololmayman.

15
@Igor Menman
qo'shib qo'ydi muallif Oli, manba
objects_standard oddiy versiya, lekin ha, bu erta maslahatlar edi. Bu aslida faqat standart model edi. Sinov modelingiz uchun order_by qo'shing va u sizni sinab ko'radi.
qo'shib qo'ydi muallif Oli, manba
Todorga minnatdorchilik bildiraman, lekin yana ham "bir ifoda sifatida ishlatiladigan subquery tomonidan qaytarilgan bir nechta satrni" ko'taradi. Men loyihada hech narsa aralashmasligiga ishonch hosil qilish uchun modelni qayta qurmoqchiman.
qo'shib qo'ydi muallif Oli, manba
Hmm, joriy javob bu mumkinligini ko'rsatadi. Mening modelimdagi biror narsa bo'lishi mumkin, men bunga ta'sir qilmayapman. Dushanba kuni men u orqali ishlayman, agar kerak bo'lsa, uni hech narsa qilmasdan va qaerga ketayotganini ko'rasiz.
qo'shib qo'ydi muallif Oli, manba
guruhlash yaratganligi uchun . .nototate dan oldin values ​​ dan foydalanmasligingiz kerak. Ushbu queryset bilan ishlay olasizmi: Carpark.objects.annotate (space_count = Subquery (Space.objects. & Zwnj; filtri (carpark = Outer & zwnj; Ref ('pk')). (& zwnj; space_cnt = Count ('pk' & zwnj;) qiymatlari ('space_cnt & zwnj;')))
qo'shib qo'ydi muallif Todor, manba
Bir buyurtma so'rovi uchun hatto buyurtma berishni ham xohlamasangiz, parametrsiz order_by() ni chaqiring.
qo'shib qo'ydi muallif Igor, manba
Ikkinchi tahrirdagi @Oli, siz maxsus menejer ( objects_standard ) dan foydalanayotganingizga o'xshaysiz. Bu sizning qo'shimcha guruhingizdan olinganmi? Bundan tashqari bookings_space da bu bo'sh joy xususiyati nima?
qo'shib qo'ydi muallif Laurent S, manba

6 javoblar

Shazaam! O'zgartirishlarimga ko'ra, pastki so'rovdan qo'shimcha ustun chiqdi. Bu buyurtma berishni engillashtirish uchun edi (bu COUNT-da talab qilinmaydi).

Men faqat oldindan belgilangan metamorodni modeldan olib tashlashim kerak edi. Buni faqat pastki so'rov uchun bo'sh .order_by() qo'shib, mumkin. Mening kodim atamalarida bu degani:

spaces = Space.objects.filter(carpark=OuterRef('pk')).order_by().values('carpark')
count_spaces = spaces.annotate(c=Count('*')).values('c')
Carpark.objects.annotate(space_count=Subquery(count_spaces))

Va u ishlaydi. Juda yaxshi. Juda zerikarli.

20
qo'shib qo'ydi
Yuz bergani kabi, Subquery mavjud pastki klassi buyruqni o'chirib tashlaydi (aslida, pastki so'rovni tartiblash uchun hech qanday sabab yo'q). Bu (juda engil) hujjatlangan, lekin ehtimol Subquery uchun hujjatlarni yaxshilash mumkin.
qo'shib qo'ydi muallif Matthew Schinckel, manba
Yo'q, buyurtmaning qo'llanilishi "tabiiy" Modeldagi ko'rsatma. Ammo, bu pastki so'rovlarni buzadi. Men xato topdim, deb o'ylayman. Repro to juda oddiy.
qo'shib qo'ydi muallif Oli, manba
Ushbu xabar uchun rahmat. Django 1.11 da keltirilgan subtrantsiyalar bo'yicha tegishli hujjatlar etarli emas. Bu hozirgacha eng yaxshi muammoni hal qiluvchi.
qo'shib qo'ydi muallif minder, manba
Nima uchun biron-bir buyurtma berishni talab qilmasligimga shubha bilan qarardim. Hech bir imkoniyat mysql-ni orqa qism sifatida ishlatasizmi? Django manbai faqat MySQL "True" ga o'rnatgan db/backends/base/features.py ichida require_explicit_null_ordering_when_grouping = noto'g'ri ga ega. Buni qaerda ishlatilganligini tekshirolmayman, faqatgina birlik testida ( github.com/django/django/blob/… ), lekin bu nima uchun bu qo'shimcha ORDER BY ni olishingizni tushuntirishi mumkin.
qo'shib qo'ydi muallif Laurent S, manba

Bundan tashqari, Subquery ning quyi sinfini yaratish mumkin. Misol uchun, siz quyidagilarni foydalanishingiz mumkin:

class SQCount(Subquery):
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = models.IntegerField()

Keyinchalik original Subquery sinfidan foydalanishingiz mumkin.

spaces = Space.objects.filter(carpark=OuterRef('pk')).values('pk')
Carpark.objects.annotate(space_count=SQCount(spaces))

Ushbu hiylani (kamida postgresda) birlashma funktsiyalari bilan ishlatishingiz mumkin: Men uni ko'pincha qadriyatlar majmuasini yaratish yoki ularni jamlash uchun ishlataman.

9
qo'shib qo'ydi
boshqa guruhiga qo'shimcha nom berishiga ishonchim komil emas, chunki pastki so'rov boshqa subquery ichida qo'llaniladi, bu bitta qiymat qaytaradi.
qo'shib qo'ydi muallif Matthew Schinckel, manba
Bu qiziqarli g'oya (va mo''tadil toifadagi nomlar bilan ham mantiqan to'g'ri), lekin agar meni tag'in bug'dek urishgan bo'lsa (qo'shimcha guruhni qo'shib buyurtma berishni aniqlaydigan namunaviy model) hali ham Subquery SQL kompozitsiyasida mavjud bo'lsa, bu unga ham ta'sir qiladi.
qo'shib qo'ydi muallif Oli, manba
Hiyla uchun rahmat! Sizning so'rovlar to'plamingizda bo'lishi mumkin bo'lgan har qanday buyurtmani olib tashlash uchun @karolyi javob bilan bu trickni birlashtirishi mumkinligini eslatib turish kerak, faqat SQCount sinfidagi resolve_expression ni tahrirlash talab etiladi.
qo'shib qo'ydi muallif Salvatore Fiorenza, manba
Ushbu ajoyib o'yin uchun tashakkur
qo'shib qo'ydi muallif Alex Mashianov, manba

Men faqat shu kabi holatga tushib qoldim, bu erda rezervasyon holatini bekor qilmaydigan hodisalar uchun joylarni qo'yish kerak edi. Muammoni bir necha soatlab echishga urinib ko'rganingizdan so'ng, men bu muammoning asosiy sababi sifatida ko'rdim:

Kirish: bu MariaDB, Django 1.11.

So'rovni izohlashda siz tanlagan maydonlarni (asosan qiymatlari() so'rovlar tanlovida nima bor) bilan GROUP BY so'zini oladi). MariaDB buyruq qatori vositasi bilan tekshiruvdan so'ng, nima uchun NULL yoki None ni so'rov natijalarida olishni boshladim, shuning uchun GROUP NULL ni qaytarish uchun COUNT() kodini keltiradi.

So'ngra, QuerySet interfeysiga sho'ngqacha kirishni boshladim, shuning uchun qo'lni qanday qilib qo'llay olaman, GROUP BY ni JB-so'rovlaridan o'chirib tashlang va quyidagi kod bilan tanishdim:

from django.db.models.fields import PositiveIntegerField

reserved_seats_qs = SeatReservation.objects.filter(
        performance=OuterRef(name='pk'), status__in=TAKEN_TYPES
    ).values('id').annotate(
        count=Count('id')).values('count')
# Query workaround: remove GROUP BY from subquery. Test this
# vigorously!
reserved_seats_qs.query.group_by = []

performances_qs = Performance.objects.annotate(
    reserved_seats=Subquery(
        queryset=reserved_seats_qs,
        output_field=PositiveIntegerField()))

print(performances_qs[0].reserved_seats)

Shunday qilib, bajarilgan vaqtda unga GROUP BY ega bo'lmasligi uchun, subqueryning so'rovlar jadvalidagi group_by maydonini qo'lda olib tashlashingiz kerak. Bundan tashqari, Django avtomatik tarzda tanib bo'lmaydigan ko'rinadi va so'rovlar sarlavhasining birinchi bahosini olib tashlash kabi ko'rinadi, pastki so'rovning qaysi chiqish maydonini ko'rsatishi kerak. Qizig'i shundaki, u holda ikkinchi baholash muvaffaqiyatsiz bo'ladi.

Menimcha, bu Django bug yoki pastki so'rovlarda samarasiz. Men bu haqda xato haqida hisobot tuzaman.

Tahrirlash: bug hisobot bu erda .

5
qo'shib qo'ydi
.values ​​(), annotate (). Values ​​() raqsidan qochish imkonini beruvchi Subquery ning maxsus kichik sinfini yozishingiz mumkin. Masalan: class Count (Subquery): shablon = "SELECT count (*) FROM (% (subquery) s) _count"
qo'shib qo'ydi muallif Matthew Schinckel, manba
Sharhlar, albatta, kod bloklarini qo'shish uchun yaxshi emas :)
qo'shib qo'ydi muallif Matthew Schinckel, manba
Ichki ichki pastki so'rovlar bo'yicha to'plash uchun izohlar() va qadriyatlarni() s (ob'ektlarni faqatgina o'zlari moslamalarni moslashtirsa, __in bilan birga ba'zi parametrlarni qondirgan holda) va @MatthewSchinckel men uchun ishlaydigan yagona echim. Menimcha, bu biroz ko'proq ko'rinishi kerak.
qo'shib qo'ydi muallif pgcd, manba
Bu to'g'ri hal, thx. Maqsadim ORM darajasida qolish va imkon qadar SQL mustaqil bo'lish edi. Shuning uchun group_by tweak.
qo'shib qo'ydi muallif karolyi, manba

To'g'ri tushunsam, Carpark da mavjud bo'lgan Space ni sanashga harakat qilmoqdasiz. Buning uchun subquery tuyuladi, yaxshi eskirgan yakka izohni bajarish kerak:

Carpark.objects.annotate(Count('spaces'))

Bu sizning natijalaringizda spaces__count qiymatini o'z ichiga oladi.


OK, sizning eslatmangizni ko'rdim ...

Men sizning qo'lingizdagi boshqa modellar bilan bir xil so'rovni bajarishga muvaffaq bo'ldim. Natijalar bir xil, shuning uchun namunadagi so'rovlar OK ko'rinadi (Django 1.11b1 bilan testlangan):

activities = Activity.objects.filter(event=OuterRef('pk')).values('event')
count_activities = activities.annotate(c=Count('*')).values('c')
Event.objects.annotate(spaces__count=Subquery(count_activities))

Ehtimol, sizning «eng oddiy real misolingiz» juda oddiy ... siz modellar yoki boshqa ma'lumotlarni almashasizmi?

2
qo'shib qo'ydi
Siz haqiqatdan ham bu erda juda yaxshi fikrni qilyapsiz (va menimcha, django docs-da, bu uyni etarlicha zabt eta olmadik): pastki so'rovlar a. annotate() ishlaydi. Umuman olganda men ularni tegishli satrlarning pastki qismidan mos yozuvlar yoki agregatlashim kerak bo'lgan joylarda ishlataman.
qo'shib qo'ydi muallif Matthew Schinckel, manba

"works for me" doesn't help very much. But. I tried your example on some models I had handy (the Book -> Author type), it works fine for me in django 1.11b1.

Buni Djangoning to'g'ri versiyasida ishlayotganingizga aminmisiz? Ishlayotgan haqiqiy kod bumi? Buni aslida carpark emas, balki bir necha murakkab modelni sinab ko'rayapsizmi?

Ma'lumotlar bazasida ishlashga harakat qilayotgan SQLni ko'rish uchun print (thequery.query) ga ishlating. Quyida mening modellarim bilan olgan narsam bor (sizning savolingizga muvofiq tahrirlangan):

SELECT (SELECT COUNT(U0."id") AS "c"
FROM "carparks_spaces" U0
WHERE U0."carpark_id" = ("carparks_carpark"."id")
GROUP BY U0."carpark_id") AS "space_count" FROM "carparks_carpark"

Albatta, javob yo'q, lekin umid qilamanki, bu yordam beradi.

1
qo'shib qo'ydi
Yaxshi maslahat va yaxshi ma'lum bo'lgan SQL uchun rahmat. Bu, aslida mening so'rovimga qo'shimcha narsa qo'shilishini ta'kidladi (qo'shimcha guruh-subquerylikda), lekin men hali ham nima uchun ga ishonch hosil qilmayapman. Men savolni noqonuniy SQL bilan tartibga solaman.
qo'shib qo'ydi muallif Oli, manba
@karolyi Bu muammolarni bilvosita tuzatishga olib kelishi mumkin edi, lekin modellashtirilgan buyurtmalarni bekor qilish men uchun buni tuzatdi. Qo'shimcha GROUP BY mavjudligi ortiqcha tartibda yuzaga kelgan.
qo'shib qo'ydi muallif Oli, manba
Buning evaziga men o'zimga yordam berishim uchun o'zimga yordam beraman, lekin o'zimning javobimni qabul qilaman, chunki u keyingi odamga yordam berishi mumkin.
qo'shib qo'ydi muallif Oli, manba
@Oli, mening yechimimga qara, men aslida django devlar bilan gaplashaman. order_by() qoidasiga hech qanday aloqasi yo'q.
qo'shib qo'ydi muallif karolyi, manba

Har qanday umumiy yig'ilish uchun ishlaydigan yechim Django 2.0 dan Window kurslari yordamida amalga oshirilishi mumkin. Buni Django izdoshlari chiptasiga qo'shdim.

Bu esa, tashqi so'rovlar modeli (GROUP BY yon tümcesinde) asosidagi qismlar ustida yig'ib hesaplayarak tushuntirib qiymatlari birleştirilmesini beradi, keyin bu ma'lumotlarni pastki so'rovlar sorgusundaki har bir satrga tushuntirish beradi. Subquery, keyinchalik qaytarilgan birinchi satrda to'plangan ma'lumotlardan foydalanishi va boshqa qatorlarni e'tiborsiz qoldirishi mumkin.

Performance.objects.annotate(
    reserved_seats=Subquery(
        SeatReservation.objects.filter(
            performance=OuterRef(name='pk'),
            status__in=TAKEN_TYPES,
        ).annotate(
            reserved_seat_count=Window(
                expression=Count('pk'),
                partition_by=[F('performance')]
            ),
        ).values('reserved_seat_count')[:1],
        output_field=FloatField()
    )
)
1
qo'shib qo'ydi
IIRC barcha ma'lumotlar bazalarini emas, balki oyna vazifalarini qo'llab-quvvatlamaydi (va ehtimol, hamma funksiyalar oyna vazifalari sifatida ishlatilishi mumkin emas). Buni eshitib, deraza narsalar ajoyib qo'shimchalar va men uni qo'llab-quvvatlovchi djangoning versiyasidan foydalanib loyiha qilishni orzu qilaman!
qo'shib qo'ydi muallif Matthew Schinckel, manba