How To Write Raw Sql Query In Odoo

I want to write raw sql query for following code. product_ids = self.env['product.product'].with_context(warehouse=warehouse_id.ids).search([]).filtered(lambda p:p.qty_available &g

Solution 1:

The product qty_available is a computed field and the compute method _compute_quantities depends on the result of _compute_quantities_dict to get the quantities dict and _get_domain_locations to parses the context and returns a list of location_ids based on it.

You need to write a query that gets the same result.

You can check the PostgreSQL log to see how many queries as executed for this python statement.

The following query is the last one executed to get the result (generated using a demo database):

SELECTmin("stock_move".id) AS id, count("stock_move".id) AS "product_id_count" , sum("stock_move"."product_qty") AS "product_qty","stock_move"."product_id" as "product_id" 
                FROM "stock_location" as "stock_move__location_dest_id","stock_location" as "stock_move__location_id","stock_move"
                WHERE ("stock_move"."location_dest_id"="stock_move__location_dest_id"."id" AND "stock_move"."location_id"="stock_move__location_id"."id") AND (((("stock_move"."state" in ('waiting','confirmed','assigned','partially_available'))  AND  ("stock_move"."product_id" in (62,41,40,15,64,65,51,16,17,18,19,20,21,23,24,55,58,60,54,56,57,61,53,52,12,13,14,25,30,26,48,39,36,49,31,34,45,42,5,8,29,43,33,38,46,32,6,27,35,28,44,37,7,50,66,59,67)))  AND  ("stock_move__location_dest_id"."parent_path"::text like'1/7/%'))  AND  (NOT (("stock_move__location_id"."parent_path"::text like'1/7/%')))) AND ("stock_move"."location_dest_id"="stock_move__location_dest_id"."id") AND (("stock_move"."company_id" in (1))  OR  "stock_move__location_dest_id"."company_id" ISNULL )
                GROUPBY "stock_move"."product_id"
                ORDERBY "id"

You need to carefully study the _compute_quantities method code to know how Odoo calculates these values.

Edit: Concider the following statement:

self.env['stock.warehouse'].search([('branch_id', '=', self.env.user.branch_id.id)], 
                                   order="id desc")

To turn the above statement to an SQL query you need to know what search method does

For example, Odoo will call _where_calc to computes the WHERE clause needed to implement the domain and automatically add the ('active', '=', 1) to the domain.

Example:

query_str

SELECT "stock_warehouse".id FROM "stock_warehouse" WHERE (("stock_warehouse"."active" =%s)  AND  ("stock_warehouse"."branch_id" =%s)) AND ("stock_warehouse"."company_id" in (%s)) ORDERBY "stock_warehouse"."id" DESC

args

[True, 1, 1]

The final query:

SELECT "stock_warehouse".id FROM "stock_warehouse" WHERE (("stock_warehouse"."active" =true)  AND  ("stock_warehouse"."branch_id" =1)) AND ("stock_warehouse"."company_id" in (1)) ORDERBY "stock_warehouse"."id" DESC