Return random row on left join in one to many

[David Orme]

Hi,

I have two tables: `tx` contains a set of named objects and then `tx_vals` contain 0 to many possible values for those objects. I want to obtain a table that contains all named objects in `tx` and one random value from `tx_vals`. I think that has to be a left join to get the null values when a tx object does not appear in tx_vals. I can obviously do this by finding all the tx rows and then running a loop of queries on tx_vals, but I wondered if there was a way to get the DAL to do it in one pass.

The table structure is:

db.define_table('tx', Field('name', 'string'))
db.tx.bulk_insert([{'name':'a'},
                   {'name':'b'},
                   {'name':'c'}])

db.define_table('tx_vals',
                Field('tx_id', 'reference tx'),
                Field('val', 'integer'))

db.tx_vals.bulk_insert([{'tx_id': 1, 'val':1},
                        {'tx_id': 1, 'val':2},
                        {'tx_id': 2, 'val':3},
                        {'tx_id': 2, 'val':4})

What I'm after is something that returns:

[('a', 1), ('b', 3), ('c', None)]

and where `a` will get 1 or 2 and `b` will get 3 or 4 randomly.

Thanks,

David