CODEWITHSUNDEEP
pythonsqlpeewee
knite
knite

Reputation: 6171

Translating SQL sub-query to Peewee ORM

I have a problem similar to Translate SQLite query, with subquery, into Peewee statement and Can peewee nest SELECT queries such that the outer query selects on an aggregate of the inner query?.

The result I'm trying to generate is: given a fruit table with rows of (type, variety, price), find the cheapest variety of each fruit. There are a couple of solutions described at http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ which work:

select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

Or:

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);

How can I Peewee-ify one or both of these?

Upvotes: 2

Views: 5161

Answers (1)

knite
knite

Reputation: 6171

Cribbing from http://charlesleifer.com/blog/techniques-for-querying-lists-of-objects-and-determining-the-top-related-item/, I have:

subquery = (Fruits
            .select(
                Fruits.type.alias('type'),
                fn.Min(Fruits.price).alias('min'))
            .group_by(Fruits.type)
            .alias('subquery'))

query = (Fruits
         .select()
         .join(subquery, on=(
             (Fruits.price == subquery.c.min) &
             (Fruits.type == subquery.c.type)
        )))

This works, but I don't understand everything it's doing. What's going on with subquery.c and why is the subquery aliased?

Upvotes: 5

Related Questions