[Python] Sum of columns from two tables related to the same table

I'm trying to understand how to do a very specific query in SQL (SQLAlchemy, but I'm first trying in SQL). I'm a beginner in SQL so forgive me if I have to use an analogy to make myself understand it better.

Basically here is the problem: I have a bag (table A) of vegetables (table B) and fruits (table C). Multiple vegetables and multiple fruits can be in the bag. Each vegetable and fruit has a price.

I need my query to return me the total amount of item (int) in the bag and the total sum of the price (Decimal). The bag can have 0 vegetable but 3 fruits and vice versa, it can have items but be free and it can also be empty and has a price (price of the bag itself). So we only filter out the bags with 0 items and 0 total_price at the end.

The best I could do is four subqueries like this:

query = select(Bag.id, Bag.purchase_datetime)

vegetable_price = select(func.sum(Vegetable.amount)).where(Vegetable.bag_id == Bag.id).scalar_subquery().label("vegetable_price_total")

vegetable_count = select(func.count(Vegetable.id)).where(Vegetable.bag_id == Bag.id).scalar_subquery().label("vegetable_count_total")

fruit_price = select(func.count(Fruit.id)).where(Fruit.bag_id == Bag.id).scalar_subquery().label("fruit_price_total")

fruit_count = select(func.count(Fruit.id)).where(Fruit.bag_id == Bag.id).scalar_subquery().label("fruit_count_total")

query = add_columns(vegetable_price, vegetable_count, fruit_price, fruit_count)


And then depending on the parameter of the route (ask only for fruits, only for vegetables, or all types), I have a query.where(fruit_price + vegetable_price > 0).

And finally I add the columns together in the object returned by the route, like this:

result = session.execute(query.distinct())
for b in result:
yield BagObject(
id=b.id
item_count=(getattr(b, "vegetable_count_total", 0) or 0) + (getattr(b, "fruit_count_total", 0) or 0),
total_price=(getattr(b, "vegetable_price_total", 0) or 0) + (getattr(b, "fruit_price_total", 0) or 0),
)


Even as a beginner in backend development, I know this is horrible. There's no way I can't do this with a way more optimized solution with less code. May I get some help from someone with more experience in SQL?

I tried other stuff like join, outerjoin. But all of them resulted in getting duplicated rows, and the price/item_count being multiplied in the final result.

Continue reading...