MVCSQL

例子1

假设销售在sale.order对象中相关的sale_order表中是一个可变记录，你可以用使用下面的代码得到这个变量:

  1. sale = self.browse(cr, uid, ID)

(数据库游标中，cr是当前行，uid是当前用户的ID,为了安全起见，ID是销售订单的ID，如果要得到多个结果的时候，ID就为多个销售订单ID的列表)

假设你想要根据第一个合作伙伴相关的销售订单ID取得相应的国家名，那么你可以在OpenERP中如下实现:

  1. country_name = sale.partner_id.address[0].country_id.name

If you want to write the same thing in traditional SQL development, it will be in python: (we suppose cr is the cursor on the database, with psycopg)

  1. cr.execute('select partner_id from sale_order where id=%d', (ID,))
  2. partner_id = cr.fetchone()[0]
  3. cr.execute('select country_id from res_partner_address where partner_id=%d', (partner_id,))
  4. country_id = cr.fetchone()[0]
  5. cr.execute('select name from res_country where id=%d', (country_id,))
  6. del partner_id
  7. del country_id
  8. country_name = cr.fetchone()[0]

Of course you can do better if you develop smartly in SQL, using joins or subqueries. But you have to be smart and most of the time you will not be able to make such improvements:

  • Maybe some parts are in others functions

  • There may be a loop in different elements

  • You have to use intermediate variables like country_id

The first operation as an object call is much better for several reasons:

  • It uses objects facilities and works with modules inheritances, overload, …

  • It’s simpler, more explicit and uses less code

  • It’s much more efficient as you will see in the following examples

  • Some fields do not directly correspond to a SQL field (e.g.: function fields in Python)

Example 2 - Prefetching

Suppose that later in the code, in another function, you want to access the name of the partner associated to your sale order. You can use this:

  1. partner_name = sale.partner_id.name

And this will not generate any SQL query as it has been prefetched by the object relational mapping engine of OpenERP.

Loops and special fields

Suppose now that you want to compute the totals of 10 sales order by countries. You can do this in OpenERP within a OpenERP object:

  1. def get_totals(self, cr, uid, ids):
  2.    countries = {}
  3.    for sale in self.browse(cr, uid, ids):
  4.       country = sale.partner_invoice_id.country
  5.       countries.setdefault(country, 0.0)
  6.       countries[country] += sale.amount_untaxed
  7.    return countries

And, to print them as a good way, you can add this on your object:

  1. def print_totals(self, cr, uid, ids):
  2.    result = self.get_totals(cr, uid, ids)
  3.    for country in result.keys():
  4.       print '[%s] %s: %.2f' (country.code, country.name, result[country])

The 2 functions will generate 4 SQL queries in total ! This is due to the SQL engine of OpenERP that does prefetching, works on lists and uses caching methods. The 3 queries are:

  1. Reading the sale.order to get ID’s of the partner’s address

  2. Reading the partner’s address for the countries

  3. Calling the amount_untaxed function that will compute a total of the sale order lines

  4. Reading the countries info (code and name)

That’s great because if you run this code on 1000 sales orders, you have the guarantee to only have 4 SQL queries.

注释:

  • IDS is the list of the 10 ID’s: [12,15,18,34, …,99]

  • The arguments of a function are always the same:

    • cr: the cursor database (from psycopg)

    • uid: the user id (for security checks)

  • If you run this code on 5000 sales orders, you may have 8 SQL queries because as SQL queries are not allowed to take too much memory, it may have to do two separate readings.

A complete example

Here is a complete example, from the OpenERP official distribution, of the function that does bill of material explosion and computation of associated routings:

  1. class mrp_bom(osv.osv):
  2.     ...
  3.     def _bom_find(self, cr, uid, product_id, product_uom, properties=[]):
  4.         bom_result = False
  5.         # Why searching on BoM without parent ?
  6.         cr.execute('select id from mrp_bom where product_id=%d and bom_id is null
  7.                       order by sequence', (product_id,))
  8.         ids = map(lambda x: x[0], cr.fetchall())
  9.         max_prop = 0
  10.         result = False
  11.         for bom in self.pool.get('mrp.bom').browse(cr, uid, ids):
  12.             prop = 0
  13.             for prop_id in bom.property_ids:
  14.                 if prop_id.id in properties:
  15.                     prop+=1
  16.             if (prop>max_prop) or ((max_prop==0) and not result):
  17.                 result = bom.id
  18.                 max_prop = prop
  19.         return result
  21.         def _bom_explode(self, cr, uid, bom, factor, properties, addthis=False, level=10):
  22.             factor = factor / (bom.product_efficiency or 1.0)
  23.             factor = rounding(factor, bom.product_rounding)
  24.             if factor<bom.product_rounding:
  25.                 factor = bom.product_rounding
  26.             result = []
  27.             result2 = []
  28.             phantom = False
  29.             if bom.type=='phantom' and not bom.bom_lines:
  30.                 newbom = self._bom_find(cr, uid, bom.product_id.id,
  31.                                         bom.product_uom.id, properties)
  32.                 if newbom:
  33.                     res = self._bom_explode(cr, uid, self.browse(cr, uid, [newbom])[0],
  34.                           factor*bom.product_qty, properties, addthis=True, level=level+10)
  35.                     result = result + res[0]
  36.                     result2 = result2 + res[1]
  37.                     phantom = True
  38.                 else:
  39.                     phantom = False
  40.             if not phantom:
  41.                 if addthis and not bom.bom_lines:
  42.                     result.append(
  43.                     {
  44.                         'name': bom.product_id.name,
  45.                         'product_id': bom.product_id.id,
  46.                         'product_qty': bom.product_qty * factor,
  47.                         'product_uom': bom.product_uom.id,
  48.                         'product_uos_qty': bom.product_uos and
  49.                                            bom.product_uos_qty * factor or False,
  50.                         'product_uos': bom.product_uos and bom.product_uos.id or False,
  51.                     })
  52.                 if bom.routing_id:
  53.                     for wc_use in bom.routing_id.workcenter_lines:
  54.                         wc = wc_use.workcenter_id
  55.                         d, m = divmod(factor, wc_use.workcenter_id.capacity_per_cycle)
  56.                         mult = (d + (m and 1.0 or 0.0))
  57.                         cycle = mult * wc_use.cycle_nbr
  58.                         result2.append({
  59.                             'name': bom.routing_id.name,
  60.                             'workcenter_id': wc.id,
  61.                             'sequence': level+(wc_use.sequence or 0),
  62.                             'cycle': cycle,
  63.                             'hour': float(wc_use.hour_nbr*mult +
  64.                                           (wc.time_start+wc.time_stop+cycle*wc.time_cycle) *
  65.                                            (wc.time_efficiency or 1.0)),
  66.                         })
  67.                 for bom2 in bom.bom_lines:
  68.                      res = self._bom_explode(cr, uid, bom2, factor, properties,
  69.                                                  addthis=True, level=level+10)
  70.                      result = result + res[0]
  71.                      result2 = result2 + res[1]
  72.             return result, result2