How to compute a databasefield with the field-id

Model:

db.define_table('orders',
    Field('customer_id', db.customer)
    Field('order_id', 'string')
    )

I want to get a special order_id like XY-150012 where XY is part of the customer name, 15 is the year and 12 the id the actual record-id of orders. I tried in the model:

db.orders.order_id.compute = lambda r: "%s-%s00%s" % (db.customer(r['customer_id']).short, str(request.now.year)[2:], r['id'])

The id is never recognized, the computation ends up as None. If I remove r['id'] from the compute-line it works.

EDIT:

After adding an extra field field('running_number', 'integer') to the model I can access this fields content. Is there a easy way to set this fields default=db.orders.id?

SOLUTION:

With Anthony´s Input, and reading about recursive selects I came up with this solution:

db.define_table('orders',
    Field('customer_id', db.customer),
    Field('order_id', 'string', default = None))

def get_order_id(id, short):
    y = str(request.now.year)[2:]
    return '%s-%s00%s' % (short, y, id)

def set_id_after_insert(fields,id):
    fields.update(id=id)

def set_order_id_after_update(s,f):
    row = s.select().first()
    if row['order_id'] == None:
        s.update_naive(order_id=get_order_id(row['id'], row['customer_id'].short)
    else:
        return

db.orders._after_insert.append(lambda f,id: set_id_after_insert(f,id))
db.orders._after_update.append(lambda s,f: set_order_id_after_update(s,f))

Solution

The problem is that the record ID is not known until after the record has been inserted in the database, as the id field is an auto-incrementing integer field whose value is generated by the database, not by web2py.

One option would be to define an _after_insert callback that updates the order_id field after the insert:

def order_after_insert(fields, id):
    fields.update(id=id)
    db(db.order.id == id).update(order_id=db.order.order_id.compute(fields))
db.order._after_insert.append(order_after_insert)

You might also want to create an _after_update callback, but in that case, be sure to use the update_naive argument in both callbacks when defining the Set (see above link for details).

Depending on how the order_id is used, another option might be a virtual field.