Peewee select query with multiple joins and multiple counts

Question

I've been attempting to write a peewee select query which results in a table with 2 counts (one for the number of prizes associated with the lottery, and the for the number of packages associated with the lottery), as well as the fields in the Lottery model.

I've managed to write select queries with 1 count working (seen below), and then I've had to convert the ModelSelects to lists and join them manually (which I think is very hacky).

I did manage to write a select query where the results were joined, but it would multiply the packages count with the prizes count (I've since lost that query).

I also tried using a .switch(Lottery) but I didn't have any luck with this.

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']

While the above code works, is there a cleaner way to write this query?

Answer 1

Your best bet is to do this with subqueries.

# 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)