7
Suppose sale is a variable on a record of the sale.order object related to the 'sale_order' table. You can acquire such a variable doing this.::
9
sale = self.browse(cr, uid, ID)
11
(where cr is the current row, from the database cursor, uid is the current user's ID for security checks, and ID is the sale order's ID or list of IDs if we want more than one)
13
Suppose you want to get: the country name of the first contact of a partner related to the ID sale order. You can do the following in Open ERP::
15
country_name = sale.partner_id.address[0].country_id.name
17
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)
19
.. code-block:: python
21
cr.execute('select partner_id from sale_order where id=%d', (ID,))
22
partner_id = cr.fetchone()[0]
23
cr.execute('select country_id from res_partner_address where partner_id=%d', (partner_id,))
24
country_id = cr.fetchone()[0]
25
cr.execute('select name from res_country where id=%d', (country_id,))
28
country_name = cr.fetchone()[0]
30
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:
32
* Maybe some parts are in others functions
33
* There may be a loop in different elements
34
* You have to use intermediate variables like country_id
36
The first operation as an object call is much better for several reasons:
38
* It uses objects facilities and works with modules inheritances, overload, ...
39
* It's simpler, more explicit and uses less code
40
* It's much more efficient as you will see in the following examples
41
* Some fields do not directly correspond to a SQL field (e.g.: function fields in Python)
43
Example 2 - Prefetching
44
-----------------------
46
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::
48
partner_name = sale.partner_id.name
50
And this will not generate any SQL query as it has been prefetched by the object relational mapping engine of Open ERP.
52
Loops and special fields
53
------------------------
56
Suppose now that you want to compute the totals of 10 sales order by countries. You can do this in Open ERP within a Open ERP object:
58
.. code-block:: python
60
def get_totals(self, cr, uid, ids):
62
for sale in self.browse(cr, uid, ids):
63
country = sale.partner_invoice_id.country
64
countries.setdefault(country, 0.0)
65
countries[country] += sale.amount_untaxed
68
And, to print them as a good way, you can add this on your object:
70
.. code-block:: python
72
def print_totals(self, cr, uid, ids):
73
result = self.get_totals(cr, uid, ids)
74
for country in result.keys():
75
print '[%s] %s: %.2f' (country.code, country.name, result[country])
77
The 2 functions will generate 4 SQL queries in total ! This is due to the SQL engine of Open ERP that does prefetching, works on lists and uses caching methods. The 3 queries are:
79
1. Reading the sale.order to get ID's of the partner's address
80
2. Reading the partner's address for the countries
81
3. Calling the amount_untaxed function that will compute a total of the sale order lines
82
4. Reading the countries info (code and name)
84
That's great because if you run this code on 1000 sales orders, you have the guarantee to only have 4 SQL queries.
88
* IDS is the list of the 10 ID's: [12,15,18,34, ...,99]
89
* The arguments of a function are always the same:
90
- cr: the cursor database (from psycopg)
91
- uid: the user id (for security checks)
92
* 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.
97
Here is a complete example, from the Open ERP official distribution, of the function that does bill of material explosion and computation of associated routings:
99
.. code-block:: python
101
class mrp_bom(osv.osv):
103
def _bom_find(self, cr, uid, product_id, product_uom, properties=[]):
105
# Why searching on `BoM without parent ?
106
cr.execute('select id from mrp_bom where product_id=%d and bom_id is null
107
order by sequence', (product_id,))
108
ids = map(lambda x: x[0], cr.fetchall())
111
for bom in self.pool.get('mrp.bom').browse(cr, uid, ids):
113
for prop_id in bom.property_ids:
114
if prop_id.id in properties:
116
if (prop>max_prop) or ((max_prop==0) and not result):
120
def _bom_explode(self, cr, uid, bom, factor, properties, addthis=False, level=10):
121
factor = factor / (bom.product_efficiency or 1.0)
122
factor = rounding(factor, bom.product_rounding)
123
if factor<bom.product_rounding:
124
factor = bom.product_rounding
127
if bom.type=='phantom' and not bom.bom_lines:
128
newbom = self._bom_find(cr, uid, bom.product_id.id,
129
bom.product_uom.id, properties)
131
res = self._bom_explode(cr, uid, self.browse(cr, uid, [newbom])[0],
132
factor*bom.product_qty, properties, addthis=True, level=level+10)
133
result = result + res[0]
134
result2 = result2 + res[1]
138
if addthis and not bom.bom_lines:
141
'name': bom.product_id.name,
142
'product_id': bom.product_id.id,
143
'product_qty': bom.product_qty * factor,
144
'product_uom': bom.product_uom.id,
147
for wc_use in bom.routing_id.workcenter_lines:
148
wc = wc_use.workcenter_id
149
cycle = factor * wc_use.cycle_nbr
151
'name': bom.routing_id.name,
152
'workcenter_id': wc.id,
155
'hour': wc_use.hour_nbr + (
156
wc.time_start+wc.time_stop+cycle*wc.time_cycle) *
157
(wc.time_efficiency or 1
159
for bom2 in bom.bom_lines:
160
res = self._bom_explode(cr, uid, bom2, factor, properties,
161
addthis=True, level=level+10)
162
result = result + res[0]
163
result2 = result2 + res[1]
164
return result, result2
added
removed
build/html/_sources/architecture/mvc_sql.txt
