Thanks a lot. As I need only the referenced table my code is: db(db.table.reference == db.referencedtable.id).select( db.referencedtable.ALL, orderby=db.referencedtable.Field, groupby=db.cm1.id, join=(db.table.on(db.referencedtable.id == db.table.reference)))
Are there some examples online for learning to make complex 'select' ? Il giorno mercoledì 15 giugno 2016 09:11:55 UTC+2, Tribo Eila ha scritto: > > 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.
