[IMP] website_profile, gamification, : add search bar in all users page

This commit adds the search bar in 'All users' page in order to filter on name or company name.
To be able to keep the position, a non stored computed field has been added on res_users
to get the position depending on the user's karma.

The podium (top 3 users) is now displayed only if there is no search applied and if the page = 1
because it has no sens anymore in other cases.

Special thanks to @jem-odoo who helped me finding smart solution for position computing.

Task ID : 1943788
PR #31321

1 files changed, 24 insertions, 0 deletions

diff --git a/addons/gamification/models/res_users.py b/addons/gamification/models/res_users.py
index f556dcc514a..6a188f2003a 100644
--- a/addons/gamification/models/res_users.py
+++ b/addons/gamification/models/res_users.py
@@ -8,6 +8,7 @@ class Users(models.Model):
     _inherit = 'res.users'
 
     karma = fields.Integer('Karma', default=0)
+    karma_position = fields.Integer('Karma position', compute="_compute_karma_position", store=False)
     badge_ids = fields.One2many('gamification.badge.user', 'user_id', string='Badges', copy=False)
     gold_badge = fields.Integer('Gold badges count', compute="_get_user_badge_level")
     silver_badge = fields.Integer('Silver badges count', compute="_get_user_badge_level")
@@ -15,6 +16,29 @@ class Users(models.Model):
     rank_id = fields.Many2one('gamification.karma.rank', 'Rank', index=False)
     next_rank_id = fields.Many2one('gamification.karma.rank', 'Next Rank', index=False)
 
+    @api.depends('karma')
+    def _compute_karma_position(self):
+        where_query = self._where_calc([])
+        self._apply_ir_rules(where_query, 'read')
+        from_clause, where_clause, where_clause_params = where_query.get_sql()
+
+        query = """
+            SELECT sub.id, sub.karma_position
+            FROM (
+                SELECT 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)
+
+        self.env.cr.execute(query, where_clause_params + [tuple(self.ids)])
+
+        position_map = {item['id']: item['karma_position'] for item in self.env.cr.dictfetchall()}
+
+        for user in self:
+            user.karma_position = position_map.get(user.id, 0)
+
     @api.multi
     @api.depends('badge_ids')
     def _get_user_badge_level(self):