How to get rows in right format

[Marko Seppälä]

Hi,

I have a database where following structure is quite common:

db.define_table('thing',

    Field('name)

    )

db.define_table('property',

    Field('name)

    )

db.define_table('thing_has_property',

    Field('thing_id', 'reference thing'),

    Field('property_id', 'reference property'),

    Field('order_number', 'integer', default=1, notnull=True)

    )

Order number is not always present, but in many cases it's necessary. Basically it tells the order of properties for the given thing.

I want to get listing of all things and their properties. I can do the following:

query = (db.thing.id == db.thing_has_property.thing_id) &
        (db.thing_has_property.property_id == db.property.id)

things_and_their_properties = db(query).select()

However, I would like to get the data in a format where I have each thing listed only once and all it's properties are in the same row in right order based on the order number. Like
things_and_their_properties = [

     ['thing1', 'p1', 'p2', 'p7'],
     ['thing2', 'p1', 'p4'],

     etc...

     ]

Currently I'm doing this kind of restructuring manually and sometimes it's quite complicated since some of those datastructures are way more complex than this. I'm wondering, is there any way to do this kind of sorting with web2py? So that I could get rows which are already in format I need in my application. I think that my need is quite common so I guess there is some kind of standard way to do this.

Thanks in advance!

[Pierre]

this might help:

db.define_table('thing_has_property',
                                  Field('property_ids','list:reference property')

it allows you to manage properties order like so : p1 = row.property_ids[0],  p2=row.property_ids[1].......etc

[Marko Seppälä]

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?

[Pierre]

not that I know...it's an advanced feature so why not use it ?

[Anthony]

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

[Marko Seppälä]

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 Boat
Alex Chair
Bob Shoes
Curt 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, Chair
Bob Shoes
Curt Boat

Should be probably some kind of dictionary containing keys person and things, and then things is list of individual things.

[Anthony]

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