-
Notifications
You must be signed in to change notification settings - Fork 1.4k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SelectQuery dictionary fields #69
Comments
First off, thank you so much for including the SQL, makes debugging so much easier :) Since a product can have multiple items, the value of the aggregate functions has meaning and that is why you get meaningful results. The big question is what you expect "price1" to be. By the "?" interpolation I gather you're using sqlite -- trying this query in postgresql would most likely not work since it is a bit stricter. |
Yes, it's a bit contrived :) In the case of the SQLite 3.7.11 it would return the same price as I'm totally fine if you don't want to allow this. It was just a Sort of related, would R() work in this case? For example: R('GROUP_CONCAT(image) AS image') |
Yeah, in my opinion this is one of those things that give ORMs a bad rap. For a detailed description of a possible solution, check out https://groups.google.com/forum/?fromgroups#!topic/peewee-orm/RLd2r-eKp7w You're doing an inner join but you want, in a sense, a right join on item and peewee doesn't support this...and as you said, the value of price1 is "indeterminate" but should probably be a list. The way I would fix this is to query Item and group by product. I'm not familiar with the group_concat function but would be interested in learning more. |
Thanks! |
If you want to try this out, its kidn of hacky but might do what you want: |
That works great, thank you! Good choice in method name :) |
With the following I can access 'min' and 'max', but not 'price1'.
products = Product.select({
Product: ['*'],
Item: ['price1', Min('price1'), Max('price1')],
}).where(category_id=id).group_by('id').join(Item)
SQL:
('SELECT t1."id", t1."brand", t1."category_id", t1."name", t1."overview", t1."details", t2."price1", MIN(t2."price1") AS min, MAX(t2."price1") AS max FROM "products" AS t1 INNER JOIN "items" AS t2 ON t1."id" = t2."product_id" WHERE t1."category_id" = ? GROUP BY t1."id" ORDER BY t1."brand" ASC, t1."name" ASC', [u'29'])
The text was updated successfully, but these errors were encountered: