Magic Query of Odoo
The more complex the system I created, the more complex the way I need to get my data. This also happens when I need to create/write data because its field relation has too complicated. This sort of problem often just appears in the middle of development. Many frameworks have poor handling template syntaxes. So what I need to do?! Thank god, Odoo syntax is completely readable. I just need to type one-or-two word to do it. Let me show you the ease.
Create
Basic operation starts with Create as it is the first letter in CRUD (Create Read Update Delete). When I want to call create method of a model from its own model, I just have to type self.create(vals_list) with vals_list is a list of dictionary values you want to insert into databases.
self.create([{'name': 'Product A', ...}, ...])Or if I want to insert into another table from a current one, I just need to add env[model] between self and create. Model is a class that maps to table. So basically to insert into product_product table, I type product.product inside. Its a rule of thumb conversion between a model and its table in Odoo. This rule is also applied to other operations.
self.env['product.product'].create([{...}])In the original query, I have to type INSERT INTO … VALUE … and match EVERY SINGLE field and its value respectively to be right. Compare with the original, what will you choose? For me, I prefer the former way. And you? I’m sure you agree with me on this one.
INSERT INTO product_product (name, ...) VALUES ('Product A', ...);In addition, there is a simple way to create an identical data. Its called duplication. The method is copy() and if I want to override just one/some values, just type the value you want after the field name inside the dictionary.
self.copy({'name': 'Product B'})Update
The next operation is Update. Wait, isn’t it Read? Yeah you right, I skip that for now because there are so many things I want to tell you about that. For now, let me show you this short operation. This operation is called when you want to update fields into one selected data (cursor cr). The syntax is write method with one dictionary inside. For example, I want to change ‘Product A’ name to ‘Product C’.
cr.write({'name': 'Product C'})That syntax is simplification of this query below.
UPDATE product_product SET name = 'Product C' WHERE id = 0;Delete
Wow, the way of calling the first two operations are so simple. What about deletion, is it the same? Yes, I just need to select the cursor and type unlink. Done.
cr.unlink()The comparison is:
DELETE FROM product_product WHERE ID = 0;Read
Finally, the last operation, Read. In terms of syntax’s searching, Odoo provides the best method. To get a better idea, I will give you an example. In a library’s PC, if you’ve known what you searching for by its ISBN, you will just directly input that into the system and search. Or if you just know the name of the writer partially, you will input in the Writer field and click Search. And so on.
Just like searching a book, the methods of searching in Odoo are no more different. Let’s give it a look.
Browse
When I’ve known the ID(s) of the data I want to read and I want all of the information inside, I pass it into browse param.
self.browse([11])That is the same as this.
SELECT * FROM product_product WHERE id in [11];Search
But when I just know the name of one or two fields, I use search method and pass the known values into the domain filter. Just like querying, there are LIMIT, ORDER, and other params. But it's all set in defaults value, There’s no limit LIMIT = None and no ordering ORDER = id asc. If you want to change the value, add it after the domain.
self.search([('name', '=', 'Product C')], LIMIT=1, ORDER='id desc')=
SELECT * FROM product_product WHERE name = 'Product C' LIMIT 1 ORDER BY ID DESC;Anyway, I just can domain filtering by its existing field. If the field is not there, in this case, is on the other table, I can’t filter with this.
Filtered
I’ve been debate a lot with my co-works whether this method is the same as the search method. At first, it looks similar. But when I pay attention, it is a c̶o̶m̶p̶l̶e̶t̶e̶l̶y̶ different method. Oh, I’m sorry, not completely. Filtered method used to handle filtering that the Search method can’t handle. It’s when I need to filter it by field’s value from a different table. For example, there are two identical ‘Product C’ but you only want the vendor that is from Indonesia.
cr = self.search([('name', '=', 'Product C')])
cr.filtered(lambda x: x.vendor_id.location == 'Indonesia')There’s only one param and it’s lambda function. That is way more readable than this one right?!
SELECT * FROM product_product product, res_partner vendor
WHERE product.name = 'Product C' AND vendor.location = 'Indonesia';Mapped
The last method Mapped serves for two purposes:
- when I need to return just a specific field, or
- to return the result of calculation.
The first example, when I want to return only the color of Product C.
cr = self.search([('name', '=', 'Product C')])
cr.mapped('color')That is the same as this.
SELECT color FROM product_product WHERE name = 'Product C';The second, when I want to combine two strings from different fields.
cr.mapped(lambda x: x.name + x.color)Awesome.
SELECT name + color FROM product_product WHERE name = 'Product C';I hope this is useful, so follow and share to get MORE explanatory articles about Odoo. See you!
