I guess you're in a small "typo hiccup" recap without "long table and field names":
db(db.what_i_need.id.belongs(db(otherquery)._select(otherfield))).select(db.what_i_need.field) you can get it rewritten (more understandable) mysubquery = db(otherquery)._select(otherfield) myquerywithbelongs = db.what_i_need.id.belongs(mysubquery) thefinalquery = db(myquerywithbelongs).select(db.what_i_need.field) You can't order inside the subselect (the one starting with the underscore, it's just a "condition"), as what you want "returned" isn't there.... it's in the "upper" select (the one without the underscore, i.e. the ACTUAL select) categories=db(db.news_categories.id.belongs(db(db.news.on_line==True). _select(db.news.category, distinct=True, orderby=db.news.category.name))). select() should be rewritten as categories=db(db.news_categories.id.belongs(db(db.news.on_line==True). _select(db.news.category, distinct=True))).select(orderby=db.news_categories .name) On Tuesday, March 10, 2015 at 9:42:31 PM UTC+1, Gael Princivalle wrote: > > Thanks all. > Niphlod what a great query. > I'm just disappointed about the orderby, it don't have any effect: > categories=db(db.news_categories.id.belongs(db(db.news.on_line==True). > _select(db.news.category, distinct=True, orderby=db.news.category.name))). > select() > > Do you know why? > > Il giorno martedì 10 marzo 2015 12:44:55 UTC+1, Niphlod ha scritto: >> >> 2 queries ? nooooo >> >> subselect - way, 1 query. >> >> db(db.news_categories.id.belongs(db(db.news.id>0)._select(db.news.category, >> distinct=True))).select() >> >> technically, there is also another one, more useful in case you'd like to >> select, e.g., only categories with at least x news, but that's another >> story. >> > -- 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.