[FIX] lunch: performance issue when retrieving lunch orders

- When retrieving lunch orders for specific users, the search could take a
  long time to be resolved.

  To fix this issue we rewrote the SQL View that retrieves the
  information for lunch orders.

closes odoo/odoo#32908

Signed-off-by: Denis Ledoux <beledouxdenis@users.noreply.github.com>

1 files changed, 3 insertions, 1 deletions

diff --git a/addons/lunch/report/lunch_product_report.py b/addons/lunch/report/lunch_product_report.py
index 140e5c73a0a..7c9fcfcb000 100644
--- a/addons/lunch/report/lunch_product_report.py
+++ b/addons/lunch/report/lunch_product_report.py
@@ -81,11 +81,13 @@ class LunchProductReport(models.Model):
                     users.id AS user_id,
                     fav.user_id IS NOT NULL AS is_favorite,
                     product.new_until >= current_date AS is_new,
-                    (SELECT date FROM lunch_order WHERE product_id = product.id AND user_id = users.id AND date <= current_date ORDER BY date DESC LIMIT 1) AS last_order_date
+                    max(l_order.date) AS last_order_date
                 FROM lunch_product product
                 RIGHT JOIN res_users users ON users.company_id = product.company_id -- multi company
                 LEFT JOIN lunch_product_favorite_user_rel fav ON fav.product_id = product.id AND fav.user_id = users.id
                 JOIN res_groups_users_rel groups ON groups.uid = users.id -- only generate for internal users
+                JOIN lunch_order l_order ON l_order.product_id = product.id AND l_order.user_id = users.id AND l_order.date <= current_date
                 WHERE users.active AND product.active AND groups.gid = %%s --only take into account active products and users
+                GROUP BY product.id, product.name, product.category_id, product.description, product.price, product.supplier_id, product.company_id, product.active, users.id, is_favorite, is_new
             );
         """ % self._table, (self.env.ref('base.group_user').id,))