0

I'm developing a db project using web2py framework where in I need to write a query to find the total bill amount that is obtained by multiplying the quantity and unit_price.

Tables:

db.define_table('item',
Field('name', notnull = True,unique=True),
Field('price'),
format = '%(name)s')

db.define_table('customer',
Field('item_name', 'reference item'),
Field('item_price','reference item' ),
Field('name', notnull = True),
Field('email',requires = IS_EMAIL(error_message='invalid email!'),unique='True'),
Field('adress',requires=IS_NOT_EMPTY()),
Field('city'),
Field('quantity'),
Field('state',default='KARNATAKA'),
Field('contact_number'),
Field('Total_price',
compute=lambda r: r['quantity'] * r['item_price']),
format='%(name)s')
db.item.name.requires = IS_NOT_IN_DB(db, db.item.name)
db.customer.item_name.requires = IS_IN_DB(db, db.item.id, '%(name)s')
db.item.price.requires = IS_NOT_IN_DB(db, db.item.price)
db.customer.item_price.requires = IS_IN_DB(db, db.item.id, '%(price)s')`

Output:

customer.id customer.item_name  customer.item_price customer.name   customer.email  customer.adress customer.city   customer.quantity   
customer.state  customer.contact_number customer.Total_price:

Gold Winner Palm    Reshma  reshmagunda@g...    Near Bus stand  Mudalgi 2   KARNATAKA   7423089630  2222

If I use this query for single table calculation it works fine, but for foreign key linked tables I'm getting junk values...

Price field is in item; for above query it is 65/- it is foreign key for customer table and quantity is 2. Expected output is 130 but generated query output is 2222.

What modifications do I need to make to get the expected output?

Toby Speight
  • 27,591
  • 48
  • 66
  • 103
Vaishnavi.B
  • 1
  • 2

1 Answers1

2

Reference fields simply store the record ID of the referenced record -- they do not store actual field values from the referenced record. So, the following field:

Field('item_price', 'reference item')

does not store a price value from the item table -- it simply stores the record ID of the particular item from the item table. To get the item price itself, you need to query the item table (this could be done via a join or a separate query).

This means that the item_name and item_price fields are redundant -- you only need a single field referencing the item table, so replace those two fields with something like:

Field('item', 'reference item')

The computed field can then be changed to:

Field('Total_price', compute=lambda r: r.quantity * db.item(r.item).price)

r.item is the record ID of the associated item, so db.item(r.item) fetches that record, and db.item(r.item).price is therefore the value of the price field from that record.

Also, note that the default field type is "string", which is the type of your price fields given that you have not explicitly specified a type. You might instead want to specify them as decimal fields.

Anthony
  • 25,466
  • 3
  • 28
  • 57