SelectQuery dictionary fields

[jdearl]

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'])

[coleifer]

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.

[jdearl]

Yes, it's a bit contrived :)

In the case of the SQLite 3.7.11 it would return the same price as MAX(price1). Also, I'm not extremely familiar with PostgreSQL, but I believe the latest versions allow such queries also.

I'm totally fine if you don't want to allow this. It was just a little confusing, because I was able to provide a list of fields to Product, but not to Item. I do agree though, price1 isn't really useful.

Sort of related, would R() work in this case? For example:

R('GROUP_CONCAT(image) AS image')

[coleifer]

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.

[jdearl]

Thanks! Btw, I didn't realize there was a peewee forum!

[coleifer]

If you want to try this out, its kidn of hacky but might do what you want: http://peewee.readthedocs.org/en/latest/peewee/querying.html#speeding-up-simple-select-queries

[jdearl]

That works great, thank you! Good choice in method name :)