Dynamic self join

Question

Consider the following model, consisting of one table cars with 3 columns:

class Car(Model):
    owner_id = IntegerField()
    name = CharField()
    power = IntegerField()

The input can be from 1 up to 5 car names (no duplicates). How do you get the owners of all cars (partial match not allowed), up to 5, and sort them by the total horse power?

Here's a graphical representation of what I'd like to achieve based on the data below, also available in this SQL fiddle:

create table cars(owner_id integer, name varchar(21), power integer);
insert into cars values (101,'bmw',300);
insert into cars values (101,'audi',200);
insert into cars values (101,'mercedes',100);
insert into cars values (102,'bmw',250);
insert into cars values (102,'mercedes',400);
insert into cars values (103,'bmw',200);
insert into cars values (103,'audi',100);
insert into cars values (103,'mercedes',190);

               ┌──────────┐
'bmw'          │          │       owner_id|total_power
'mercedes' ───►│  query   ├───►   --------|-----------
               │          │            102|        650
               └──────────┘            101|        400
                                       103|        390
               ┌──────────┐
'bmw'          │          │       owner_id|total_power
'mercedes' ───►│  query   ├───►   --------|-----------
'audi'         │          │            101|        600
               └──────────┘            103|        490

With 2 cars, I tried this (it doesn't work):

select owner_id, sum(power) as total_power
from cars c1
inner join cars c2 on c1.owner_id = c2.owner_id
where c1.name = 'bmw' and c2.name = 'audi'
group by owner_id
order by total_power desc

I have no idea:

• if there's a better way to write this for 5 cars or if 5 joins are the only way
• how to make the sum of all power
• how to write this in peewee

For non-peewee answers, I'm using SQLite.

Thanks

Answer 1

Use a CTE that returns the input car names to filter the table and use aggregation to get the total power for each owner_id.

Place the final filter in the HAVING clause:

WITH cte AS (VALUES ('bmw'), ('mercedes'))
SELECT owner_id, SUM(power) power
FROM cars
WHERE name IN cte
GROUP BY owner_id
HAVING COUNT(DISTINCT name) = (SELECT COUNT(*) FROM cte)
ORDER BY SUM(power) DESC;

See the demo.