I'm not sure there is a "standard" way, but if you want to use the DAL API to generate the query, then you'll have to manipulate the data using Python after retrieving the results.
Alternatively, you could generate the SQL code manually and use group_concat (if using MySQL or SQLite -- there are similar alternatives for Postgres and others) to get the database to return the results in the format you want. Once you have the SQL code, you can use db.executesql() to run the query. Anthony On Wednesday, May 4, 2016 at 4:01:49 AM UTC-4, Marko Seppälä wrote: > > I need to do very complex queries. Actually so complex that I don't even > know how to do it properly, even though I managed to get it work without > list reference. I discussed with one expert yesterday and he recommended > not to use list reference in my case. > > So currently I'm wondering what is the efficient way to reorganize my data > to right format. Following example from web2py book's is usually part of my > queries: > > >>> persons_and_things = db((db.person.id == db.ownership.person) & > (db.thing.id == db.ownership.thing)) > > >>> for row in persons_and_things.select(): > print row.person.name, row.thing.name > Alex BoatAlex ChairBob ShoesCurt Boat > > Is there any "standard" way to get the results in the following format, > even though you're not using list reference? That format is more natural to > handle in controllers and views, so I need to reorganize the data somehow. > > Alex Boat, ChairBob ShoesCurt Boat > > > Should be probably some kind of dictionary containing keys person and > things, and then things is list of individual things. > > > maanantai 2. toukokuuta 2016 20.11.33 UTC+3 Anthony kirjoitti: >> >> On Sunday, May 1, 2016 at 3:17:49 PM UTC-4, Marko Seppälä wrote: >>> >>> It works, thanks! I forgot the whole thing. When I started with web2py, >>> one expert recommended me not to use list references but do all >>> intermediary tables yourself. >>> >>> Is there any drawbacks to use list reference instead of doing those >>> intermediary tables yourself? >>> >> >> It depends on how you will need to query the data. If you typically just >> need to list the property IDs for individual "things", then list:reference >> might be a reasonable approach. However, if you need to do more complex >> queries involving attributes of things and their properties, then your >> original approach will allow more flexibility. >> >> Anthony >> > -- 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.
