[IMP] website_sale: optimize category search

Use the pivot table instead of a mapping on all found products (which does not
scale well at all when there are many products found).

On a test case of 10000 products and 300 categories (including parent/children),
testing the /shop page with a search:

- number of queries from 138 down to 128 queries,
- total request time from 0.55s down to 0.40s
- time of the changed line from 32% of the total request time down to 10%

PR: #34249

2 files changed, 7 insertions, 5 deletions

diff --git a/addons/website_sale/controllers/main.py b/addons/website_sale/controllers/main.py
index 260cb160359..46fdba8813f 100644
--- a/addons/website_sale/controllers/main.py
+++ b/addons/website_sale/controllers/main.py
@@ -259,13 +259,14 @@ class WebsiteSale(http.Controller):
         Product = request.env['product.template'].with_context(bin_size=True)
 
         search_product = Product.search(domain)
+        website_domain = request.website.website_domain()
+        categs_domain = [('parent_id', '=', False)] + website_domain
         if search:
-            categories = search_product.mapped('public_categ_ids')
-            search_categories = Category.search([('id', 'parent_of', categories.ids)] + request.website.website_domain())
-            categs = search_categories.filtered(lambda c: not c.parent_id)
+            search_categories = Category.search([('product_tmpl_ids', 'in', search_product.ids)] + website_domain).parents_and_self
+            categs_domain.append(('id', 'in', search_categories.ids))
         else:
             search_categories = Category
-            categs = Category.search([('parent_id', '=', False)] + request.website.website_domain())
+        categs = Category.search(categs_domain)
 
         if category:
             url = "/shop/category/%s" % slug(category)
diff --git a/addons/website_sale/models/product.py b/addons/website_sale/models/product.py
index 6f3adbb2bd1..056cbce0541 100644
--- a/addons/website_sale/models/product.py
+++ b/addons/website_sale/models/product.py
@@ -163,6 +163,7 @@ class ProductPublicCategory(models.Model):
                                 help="Small-sized image of the category. It is automatically "
                                 "resized as a 64x64px image, with aspect ratio preserved. "
                                 "Use this field anywhere a small image is required.")
+    product_tmpl_ids = fields.Many2many('product.template', relation='product_public_category_product_template_rel')
 
     @api.model
     def create(self, vals):
@@ -209,7 +210,7 @@ class ProductTemplate(models.Model):
     website_style_ids = fields.Many2many('product.style', string='Styles')
     website_sequence = fields.Integer('Website Sequence', help="Determine the display order in the Website E-commerce",
                                       default=lambda self: self._default_website_sequence())
-    public_categ_ids = fields.Many2many('product.public.category', string='Website Product Category',
+    public_categ_ids = fields.Many2many('product.public.category', relation='product_public_category_product_template_rel', string='Website Product Category',
                                         help="The product will be available in each mentioned e-commerce category. Go to"
                                         "Shop > Customize and enable 'E-commerce categories' to view all e-commerce categories.")