Instead using: db(db.table.reference == db.referencedtable.id).select(db.table.ALL, orderby =db.referencedtable.Field, groupby=db.table.reference)
Use: db(db.table.reference == db.referencedtable.id).select( db.referencedtable.Field, orderby=db.referencedtable.Field, groupby=db.table.reference, join=(db.table.on(db.referencedtable.id == db.table.reference))) On Tuesday, June 14, 2016 at 4:55:43 PM UTC+3, Gael Princivalle wrote: > > With inner joins orderby works but not groupby. > > db(db.table.reference == db.referencedtable.id).select(db.table.ALL, > orderby=db.referencedtable.Field) > Works. > > db(db.table.reference == db.referencedtable.id).select(db.table.ALL, > orderby=db.referencedtable.Field, groupby=db.table.reference) > Give a Ticket: > column "table.id <http://products.id>" must appear in the GROUP BY clause > or be used in an aggregate function LINE 1: SELECT "table"."id"... > > Someone have a solution ? > > Il giorno martedì 14 giugno 2016 09:59:54 UTC+2, Tribo Eila ha scritto: >> >> Try INNER JOINS. >> >> On Monday, June 13, 2016 at 6:31:33 PM UTC+3, Gael Princivalle wrote: >>> >>> New model: >>> db.define_table('categories): >>> Field('title', type='string')) >>> >>> db.define_table('products'): >>> Field('code', type='string'), >>> Field('category', reference 'categories')) >>> >>> With groupby I obtain a good result. >>> >>> results = db(db.products).select(db.products.category, groupby=db. >>> products.category) >>> >>> But I need to order the results by the category title. >>> >>> If I do like that: >>> results = db(db.products).select(db.products.category, orderby=db. >>> product.category.title, groupby=db.products.category) >>> >>> Ticket is : 'Field' object has no attribute 'title' >>> >>> Is there a way to order these results, or make a query that selct only >>> the categories in db.categories that are in db.products ? >>> >>> Il giorno giovedì 9 giugno 2016 12:56:57 UTC+2, tim.n...@conted.ox.ac.uk >>> ha scritto: >>>> >>>> Looks like you want a subquery. You'll need to decide which of the >>>> items to choose when you have a given category_code. >>>> Say you want the first item with a given category code: >>>> >>>> sub_query = db()._select(db.products.id.min(), groupby=db.products. >>>> category_code) >>>> results = db(db.products.id.belongs(sub_query)).select() >>>> >>>> >>>> On Wednesday, 8 June 2016 15:10:33 UTC+1, Gael Princivalle wrote: >>>> >>>>> Hello. >>>>> >>>>> Is there a way to select some rows without duplicates for a specific >>>>> field ? >>>>> >>>>> MODEL: >>>>> db.define_table('products'): >>>>> Field('code', type='string'), >>>>> Field('category_code', type='integer')) >>>>> >>>>> ROWS: >>>>> code / category_code >>>>> A1125 / 3 >>>>> C2214 / 2 >>>>> D235 / 3 >>>>> Z886 / 1 >>>>> >>>>> I would like to make a select that give this result: >>>>> code / category_code >>>>> A1125 / 3 >>>>> C2214 / 2 >>>>> Z886 / 1 >>>>> >>>>> Someone have an idea ? >>>>> >>>>> Thanks. >>>>> >>>>> >>>>> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
