4 ms·
From the sample on the front page SELECT "c"."id" FROM "Customer" "c" LEFT JOIN "Order" "order-1" ON "c"."id" = "order-1"."customer" GROUP BY "
by aleyan 14y ago
From the sample on the front page
SELECT "c"."id"
FROM "Customer" "c"
LEFT JOIN "Order" "order-1"
ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"
HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000
is translated into
select(c for c in Customer
if sum(c.orders.price) > 1000)
How does the python version handle nulls? They are explicitly coalesced into 0s in SQL. In python sum([1,numpy.NaN]) evaluates to nan. I think this code will do something wrong.
I am not sure what flavor of SQL is beind used, but why are the table names and the field names surrounded by double quotes? They make the query look unnecesierily hairy.
Good job otherwise. Always glad to see people contributing to the community.
- kozlovsky 14y agoNo, it is the other way, Python is translated to SQL, not vice versa ;) According to SQL standard, aggregate functions must skip NULL values. The problem is with queries which return no rows. In this case, intuitive expected result of SUM aggregate function is zero, but, because of some quirk in SQL standard, SUM must return NULL if no rows were found. Without coalesce function, result of SUM will be returned as None if no rows were found. Coalesce replaces this surprising None with zero.
- aleyan 14y agoDuh, of course. This is what happens when I don't read the explanation and just grab on to the first pieces of code I see. Thanks.