Use list of ids in SQL query in Odoo

Question

Need to pass list of ids to sql query in where clause. product_id is many2many field. My code is

query = """
                    SELECT pt.name as product,sl.price_unit as price,sum(sl.qty_invoiced) as quantity from sale_order_line sl
                    JOIN sale_order so ON so.id = sl.order_id
                    JOIN product_product pp ON pp.id = sl.product_id
                    JOIN product_template pt ON pt.id = pp.product_tmpl_id
                    WHERE so.date_order >='"""+str(obj.start_date)+"""' 
                    and so.date_order <= '"""+str(obj.end_date)+"""' 
                    and so.partner_id ="""+str(obj.customer_id.id)+""" 
                    and sl.invoice_status = 'invoiced'
        """ 
    if obj.product_id:
        query +=""" and sl.product_id in """+str(obj.product_id.ids)
    query += """GROUP BY product,price"""

syntax error sl.product_id in [13017, 11253, 1395] near '['

@TerrencePoe for my report. product_id is many2many field. so if there are two or more products need report of that products only — Thayif kabir
– Thayif kabir, Commented Jul 20, 2019 at 7:30

Thayif kabir · Accepted Answer · 2019-07-22 04:45:02Z

2

Got solution, convert list of ids to tuple

if obj.product_id:
        query +=""" and sl.product_id in %s"""
    query += """GROUP BY product,price"""
    self.env.cr.execute(query, [tuple(obj.product_id.ids)])

edited Jul 22, 2019 at 4:45

answered Jul 19, 2019 at 10:04

Thayif kabir

7266 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charif DZ · Accepted Answer · 2019-07-22 08:09:13Z

2

It's not recommanded to render the params directly in the query like that it's a bad habit you should not do it in any programming language (rick of SQL injection).

just use a tuple of params that you pass to execute call.

        query = """
                            SELECT pt.name as product,sl.price_unit as price,sum(sl.qty_invoiced) as quantity 
                            FROM sale_order_line sl
                                JOIN sale_order so ON so.id = sl.order_id
                                JOIN product_product pp ON pp.id = sl.product_id
                                JOIN product_template pt ON pt.id = pp.product_tmpl_id
                            WHERE so.date_order >= %s 
                                and so.date_order <= %s 
                                and so.partner_id = %s 
                                and sl.invoice_status = 'invoiced'
                """ 


        # remember to keep the params in the same order when you use a tuple for params
        # you can use dictionary, you can read about it, instead of %s you write %(some_key)s
        query_params = (obj.start_date,
                        obj.end_date,
                        obj.customer_id.id)
        if obj.product_id:
                query += """ and sl.product_id in  %s """
                query_params += (tuple(obj.product_id.ids),)

        query += """ GROUP BY product,price """

        self.env.cr.execute(query, query_params)

edited Jul 22, 2019 at 8:09

answered Jul 20, 2019 at 15:35

Charif DZ

14.8k3 gold badges25 silver badges42 bronze badges

3 Comments

Thayif kabir Over a year ago

While using getting an error TypeError: not all arguments converted during string formatting

Thayif kabir Over a year ago

Thanks for the response, I've updated my answer in order to solve the above mentioned issue

Charif DZ Over a year ago

yes sorry my mistake forget to put the tuple of ids in a tuple it self query_params += (tuple(obj.product_id.ids),)

ScaisEdge · Accepted Answer · 2019-07-21 10:42:43Z

1

In SQL a IN clause should use () not [] so try format your IN sequence of value in this way

sl.product_id in (3017, 11253, 1395)

edited Jul 21, 2019 at 10:42

answered Jul 19, 2019 at 7:27

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

Thayif kabir Over a year ago

Thanks for your response. i already know that sql take () not []. Is there any simple way to convert [13017, 11253, 1395] to (3017, 11253, 1395)

Kenly Over a year ago

Use tuple() to convert the list of ids.

Collectives™ on Stack Overflow

Use list of ids in SQL query in Odoo

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related