我一直在嘗試編寫一個 peewee 選擇查詢,它會生成一個包含 2 個計數的表(一個是與彩票相關的獎品數量,一個是與彩票相關的包裹數量),以及彩票模型。
我設法編寫了 1 個計數工作的選擇查詢(見下文),然後我不得不將 ModelSelects 轉換為列表並手動加入它們(我認為這很hacky)。
我確實設法編寫了一個連接結果的選擇查詢,但它會將包裹計數與獎品計數相乘(此後我丟失了該查詢)。
我也嘗試過使用 .switch(Lottery) 但我對此沒有任何運氣。
query1 = (Lottery.select(Lottery,fn.count(Package.id).alias('packages'))
.join(LotteryPackage)
.join(Package)
.order_by(Lottery.id)
.group_by(Lottery)
.dicts())
query2 = (Lottery.select(Lottery.id.alias('lotteryID'), fn.count(Prize.id).alias('prizes'))
.join(LotteryPrize)
.join(Prize)
.group_by(Lottery)
.order_by(Lottery.id)
.dicts())
lottery = list(query1)
query3 = list(query2)
for x in range(len(lottery)):
lottery[x]['prizes'] = query3[x]['prizes']
雖然上面的代碼有效,但有沒有更簡潔的方法來編寫這個查詢?
最好的辦法是用子查詢來做到這一點。
# Create query which gets lottery id and count of packages.
L1 = Lottery.alias()
subq1 = (L1
.select(L1.id, fn.COUNT(LotteryPackage.package).alias('packages'))
.join(LotteryPackage, JOIN.LEFT_OUTER)
.group_by(L1.id))
# Create query which gets lottery id and count of prizes.
L2 = Lottery.alias()
subq2 = (L2
.select(L2.id, fn.COUNT(LotteryPrize.prize).alias('prizes'))
.join(LotteryPrize, JOIN.LEFT_OUTER)
.group_by(L2.id))
# Select from lottery, joining on each subquery and returning
# the counts.
query = (Lottery
.select(Lottery, subq1.c.packages, subq2.c.prizes)
.join(subq1, on=(Lottery.id == subq1.c.id))
.join(subq2, on=(Lottery.id == subq2.c.id))
.order_by(Lottery.name))
for row in query.objects():
print(row.name, row.packages, row.prizes)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句