DAL: tables that reference tables that reference tables...

[Ryan Matlock]

It appears that there are two ways to get a field to reference another table,

Field("my_field", requires=IS_IN_DB(db, db.some_table))

and

Field("my_field_2", db.some_table_2)

The problem with both of these (and especially the first one), is that I can't seem to get the format for my_field to grab the format of some_table and my_field_2 can't grab the format from tables that some_table_2 uses in its format.

To be more concrete about it, I'm building an application that tracks service work done on vehicles.  I'd like to have a table of manufacturers, then a table that references the manufacturers and associates them with models, then configurations of those models, then particular vehicles that associate a configuration and an owner with other data like the VIN.  In both formulations that I've tried, the admin interface becomes incredibly confusing because I'm mostly having to remember what id is associated with a particular entry.  Here's a simplified version of my code so far:

db.define_table("test_person",

                Field("fname",

                      requires=IS_NOT_EMPTY()),

                Field("lname",

                      requires=IS_NOT_EMPTY()),

                format = "%(fname)s %(lname)s")

db.define_table("test_manufacturer",

                Field("name"),

                format = "%(name)s")

db.define_table("test_model",

                Field("manufacturer",

                      requires=IS_IN_DB(db, db.test_manufacturer)),

                Field("model_year"),

                Field("model"),

                format = "%(model_year)s %(manufacturer)s %(name)s")

db.define_table("test_model_configuration",

                Field("model",

                      requires=IS_IN_DB(db, db.test_model)),

                Field("config",

                      label="Configuration"),

                format = "%(model)s %(config)s")

db.define_table("test_vehicle",

                Field("vehicle_owner",

                      requires=IS_IN_DB(db, db.test_person)),

                Field("model_config",

                      requires=IS_IN_DB(db, db.test_model_configuration),

                      label="model"),

                Field("vin",

                      label="VIN"),

                format = "%(vehicle_owner)s's %(model)s")

db.define_table("test_model_2",

                Field("manufacturer",

                      db.test_manufacturer),

                Field("model_year"),

                Field("model"),

                format = "%(model_year)s %(manufacturer)s %(model)s")

db.define_table("test_model_configuration_2",

                Field("model",

                      db.test_model_2),

                Field("config",

                      label="Configuration"),

                format = "%(model)s %(config)s")

db.define_table("test_vehicle_2",

                Field("vehicle_owner",

                      db.test_person),

                Field("model_config",

                      db.test_model_configuration_2,

                      label="model"),

                Field("vin",

                      label="VIN"),

                format = "%(vehicle_owner)s's %(model)s")

I suppose I could get this to work if I had a "flatter" database.  That is, if I stuffed the manufacturer, model, and configuration into a single table, the vehicle would only be "looking up" one table and could grab all the information it needed.  However, I can see this being a problem when I want to start doing things with the vehicles (e.g. "John Doe's 1999 Honda Accord LX"), which I don't want to be displayed as "82's 16 1" for which I'd have to look up the id numbers to make any sense of it.

If I need to ignore the admin interface entirely during this initial testing phase, I'm still not sure what the most Pythonic/web2pythonic way of getting all the information into my views is.  My guess is that it would be done in the controller by making a dictionary associating id numbers with desired formats, but that seems like it would require a lot of looping, which would bog everything down in a large-scale application.

Basically, I'm completely stumped as to the "right" way to proceed.

[Alex]

from the book:
requires is a validator or a list of validators. This is not used by the DAL, but it is used by SQLFORM

therefor fields like

Field("manufacturer", requires=IS_IN_DB(db, db.test_manufacturer)),

will probably get the default type (string). So you should define it with the apropriate type instead:
Field("manufacturer", db.test_manufacturer, requires=IS_IN_DB(db, db.test_manufacturer)),

regards,
Alex

[Anthony]

On Tuesday, January 28, 2014 2:13:27 PM UTC-5, Ryan Matlock wrote:

It appears that there are two ways to get a field to reference another table,

Field("my_field", requires=IS_IN_DB(db, db.some_table))

and

Field("my_field_2", db.some_table_2)

Note, these are not two different ways to get a field to reference another table. Only the second method creates an actual "reference" field type. You'll notice it behaves similarly to the first in forms because the second implicitly gets an IS_IN_DB validator, just like the first. You should definitely use the second method for reference fields.

 

The problem with both of these (and especially the first one), is that I can't seem to get the format for my_field to grab the format of some_table and my_field_2 can't grab the format from tables that some_table_2 uses in its format.

When you do Field('myfield', 'reference othertable'), the field gets a default validator like IS_IN_DB(db, db.othertable._id, db.othertable._format), and it gets a default "represent" attribute like lambda id, r: db.other_table._format % db.other_table(id).

Note, the _format attributes do not propagate through multiple tables. So, if your test_model table has:

format = "%(model_year)s %(manufacturer)s %(name)s"

the %(manufacturer)s part of the format attribute will just refer to the actual integer ID value stored in the db.test_model.manufacturer field. It will not look up the _format attribute of the db.manufacturer table and retrieve the db.manufacturer.name value. To do the latter, you have to be explicit:

format = lambda r: '%s %s %s' % (r.model_year, r.manufacturer.name, r.name)

The _format attribute can be a function that takes a row from the table and returns whatever you want. In the above, r.manufacturer.name is a recursive select -- it selects the record from db.manufacturer with id==r.manufacturer, and then extracts the "name" field value from that record.

Be careful about including recursive selects in the _format attribute (which will then be used to construct the IS_IN_DB validator and "represent" attributes of fields that reference the table). There will be a separate recursive select for every record (e.g., every item in the drop-down created by the IS_IN_DB validator in forms, and every row displayed in a SQLFORM.grid). This can be quite inefficient if there are many records. An alternative is to use an IS_IN_SET validator instead, and construct the options by doing a multi-table join query (probably a good idea to cache the results as well).

Anthony

[Ryan Matlock]

That's working exactly as I want it to.  I'm just in the initial building phase right now, and given the purpose of the application, speed isn't critical.  Still, it's nice to know that it's more efficient to use IS_IN_SET.

I should have known that requires(db, db.something) and db.something are different because it doesn't seem like web2py would have been designed with more than one way to perform a given task.  Thanks for the insight!

On Tuesday, January 28, 2014 11:13:27 AM UTC-8, Ryan Matlock wrote:

It appears that there are two ways to get a field to reference another table,

Field("my_field", requires=IS_IN_DB(db, db.some_table))

and

Field("my_field_2", db.some_table_2)

The problem with both of these (and especially the first one), is that I can't seem to get the format for my_field to grab the format of some_table and my_field_2 can't grab the format from tables that some_table_2 uses in its format.

[Ryan Matlock]

Now that I've spent a little more time implementing your answer, I've noticed that the answers are sorted by id.  Is there any way to take the table object and sort it?  I've already tried .sorted(), but as expected, it didn't work.  This seems like the sort of thing you'd do in a controller, so I can understand why you wouldn't be able to do this in db.py.

[Anthony]

On Thursday, January 30, 2014 5:06:30 PM UTC-5, Ryan Matlock wrote:

Now that I've spent a little more time implementing your answer, I've noticed that the answers are sorted by id.  Is there any way to take the table object and sort it?  I've already tried .sorted(), but as expected, it didn't work.  This seems like the sort of thing you'd do in a controller, so I can understand why you wouldn't be able to do this in db.py.

By default, there is no explicit sorting -- the order is whatever the database returns (which will generally be ordered by id). To change the order, you'll have to specify the IS_IN_DB validator explicitly:

Field('manufacturer', 'reference test_manufacturer',
      requires=IS_IN_DB(db, 'test_manufacturer.id', db.test_manufacturer._format, sort=True))

That will sort alphabetically by the labels. You can also sort based on some field or fields in db.test_manufacturer by specifying the orderby argument:

Field('manufacturer', 'reference test_manufacturer',
      requires=IS_IN_DB(db, 'test_manufacturer.id', db.test_manufacturer._format,
                        orderby=db.test_manufacturer.name))

Anthony