Merge lp:~hbrunn/ocb-addons/trunk_lp1237832 into lp:ocb-addons

[Holger Brunn (Therp)]

Automatically derived from https://code.launchpad.net/~therp-nl/openobject-addons/trunk_lp1237832 for https://code.launchpad.net/~openerp/openobject-addons/trunk.

[Christophe CHAUVET (christophe-chauvet)]

Hi

After review the query, it's better to remove the IN operator and use the natural join with the recursive query (Explain plan is better)

It's ok for me.

See a better like this

WITH RECURSIVE account_analytic_account_hierarchy(id)
AS (select id
      from account_analytic_account
     where id=%s
    union all
    select account_analytic_account.id
      from account_analytic_account
      join account_analytic_account_hierarchy
        on account_analytic_account.parent_id= account_analytic_account_hierarchy.id)
SELECT SUM(amount)
FROM account_analytic_line, recursive account_analytic_account_hierarchy
WHERE account_id = recursive account_analytic_account_hierarchy.id
...

review: Approve (code review, no test)

[Stefan Rijnhart (Opener) (stefan-opener)]

review: Approve (test)

[Maxime Chambreuil (http://www.savoirfairelinux.com) (max3903)]

review: Approve (code review)

=== modified file 'account_budget/account_budget.py'
--- account_budget/account_budget.py	2012-10-23 16:05:04 +0000
+++ account_budget/account_budget.py	2013-10-10 09:04:36 +0000
@@ -126,7 +126,21 @@
             if context.has_key('wizard_date_to'):
                 date_to = context['wizard_date_to']
             if line.analytic_account_id.id:
-                cr.execute("SELECT SUM(amount) FROM account_analytic_line WHERE account_id=%s AND (date "
+                cr.execute("SELECT SUM(amount) FROM account_analytic_line WHERE account_id in "
+                       """(with recursive account_analytic_account_hierarchy(id)
+                        as 
+                            (
+                                select id from account_analytic_account 
+                                    where id=%s
+                                union all
+                                select account_analytic_account.id from 
+                                    account_analytic_account 
+                                    join account_analytic_account_hierarchy
+                                    on account_analytic_account.parent_id=
+                                        account_analytic_account_hierarchy.id
+                            )"""
+                       "select id from account_analytic_account_hierarchy) "
+                       "AND (date "
                        "between to_date(%s,'yyyy-mm-dd') AND to_date(%s,'yyyy-mm-dd')) AND "
                        "general_account_id=ANY(%s)", (line.analytic_account_id.id, date_from, date_to,acc_ids,))
                 result = cr.fetchone()[0]