What is the best way to get distinct order ids from the order lines?

Hello Lionel,

I guess you are absolutely right.

I salute you for this thread. We should use cr.execute('select distinct order_id.....') here.

The aim is to get the key as sale order id only and this change propsed by you will really help to speed up if the SO has many lines.

Thanks again.

Good point indeed, we can add it to the guidelines with both proposed solutions (read + set or select distinct), with examples to help people choose the best solution.

Indeed, in this specific context using a SELECT DISTINCT is fine, but normally we advice people to always use the ORM in order to correctly apply all the security rules and ORM features, and as importantly to avoid writing SQL code all over the place (and avoid introducing SQL injection vectors, etc.)

See also these existing guidelines 2.3 and 2.4:
   http://doc.openerp.com/contribute/15_guidelines/coding_guidelines.html (as soon as the website is back)

Lionel, can you contribute a new guideline for the Coding Guidelines with this example and these explanations, if you know how?

Thanks,

I think the best would be to have add a parameter "disctint=True" to the search() method of the ORM.
I'll file a bug for that.
Lionel

Thanks for your answers.
Is this the right way to do it with a SQL query?
    def _get_orders(self, cr, uid, ids, context=None):
        """Return the list of distinct Order ids in the Order Lines."""
        cr.execute("""
            SELECT DISTINCT order_id
            FROM sale_order_line
            WHERE id IN %s""",
            tuple(ids))
        return [x[0] for x in cr.fetchall()]

Lionel

Sorry, that didn't work - would it be right this way?
    def _get_orders(self, cr, uid, ids, context=None):
        """Return the list of distinct Order ids in the Order Lines."""
        cr.execute("""
            SELECT DISTINCT order_id
            FROM sale_order_line
            WHERE id IN %s""",
            (tuple(ids),)
        )
        return [x[0] for x in cr.fetchall()]
Lionel

Numerigraphe wrote:
> def _get_orders(self, cr, uid, ids, context=None):
> """Return the list of distinct Order ids in the Order Lines."""
> cr.execute("""
> SELECT DISTINCT order_id
> FROM sale_order_line
> WHERE id IN %s""",
> (tuple(ids),)
> )
> return [x[0] for x in cr.fetchall()]

That looks okay.

And for the Python version (which I really think should be the default choice in most cases unless/until we provide an API for distincts), you could have something like this using a nice generator comprehension:

   return set(line['order_id'] for line in self.read(cr, uid, ids, ['order_id'])

or even simpler using a more elegant browse:

   return set(line.order_id for line in self.browse(cr, uid, ids))

Looks much better than raw SQL code if you ask me, and comes with all the advantages of the ORM, as explained previously.