[FIX] website_profile, gamification : compute rank for website_published users only

If a non website_published user was 2nd in karma position, the all users page was
displaying in the top 3 a user with position 4 (in rank) as "normal users" cannot see
non published users.

The field karma_position is now deprecated and will be removed in master.

Task ID : 2000687
PR #33475

Signed-off-by: Thibault Delavallee (tde) <tde@openerp.com>

1 files changed, 28 insertions, 1 deletions

diff --git a/addons/website_profile/controllers/main.py b/addons/website_profile/controllers/main.py
index 9c6d31c2efc..19f9e117ded 100644
--- a/addons/website_profile/controllers/main.py
+++ b/addons/website_profile/controllers/main.py
@@ -198,7 +198,6 @@ class WebsiteProfile(http.Controller):
     # ---------------------------------------------------
     def _prepare_all_users_values(self, user):
         return {
-            'position': user.karma_position,
             'id': user.id,
             'name': user.name,
             'company_name': user.company_id.name,
@@ -226,6 +225,12 @@ class WebsiteProfile(http.Controller):
         users = User.sudo().search(dom, limit=self._users_per_page, offset=pager['offset'], order='karma DESC')
         user_values = [self._prepare_all_users_values(user) for user in users]
 
+        # Get karma position for users (only website_published)
+        position_domain = [('karma', '>', 1), ('website_published', '=', True)]
+        position_map = self._get_users_karma_position(position_domain, users.ids)
+        for user in user_values:
+            user['position'] = position_map.get(user['id'], 0)
+
         values = {
             'top3_users': user_values[:3] if not search_term and page == 1 else None,
             'users': user_values[3:] if not search_term and page == 1 else user_values,
@@ -233,6 +238,28 @@ class WebsiteProfile(http.Controller):
         }
         return request.render("website_profile.users_page_main", values)
 
+    def _get_users_karma_position(self, domain, user_ids):
+        Users = request.env['res.users']
+        where_query = Users._where_calc(domain)
+        Users._apply_ir_rules(where_query, 'read')
+        from_clause, where_clause, where_clause_params = where_query.get_sql()
+
+        # we search on every user in the DB to get the real positioning (not the one inside the subset)
+        # then, we filter to get only the subset.
+        query = """
+            SELECT sub.id, sub.karma_position
+            FROM (
+                SELECT "res_users"."id", row_number() OVER (ORDER BY res_users.karma DESC) AS karma_position
+                FROM {from_clause}
+                WHERE {where_clause}
+            ) sub
+            WHERE sub.id IN %s
+            """.format(from_clause=from_clause, where_clause=where_clause)
+
+        request.env.cr.execute(query, where_clause_params + [tuple(user_ids)])
+
+        return {item['id']: item['karma_position'] for item in request.env.cr.dictfetchall()}
+
     # User and validation
     # --------------------------------------------------