Sort contributed projects on profile page by last_activity_at

What does this MR do and why?

The list of contributed projects (on personal profile page) is now ordered by the project id field. This MR will change that to last_activity_at, so it is consistent with project lists on group pages.

The specs are now expecting the returned project list to be sorted.

Changelog: changed

Related issue: #432410 (closed)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

http://localhost:3000/users/root/contributed

Before	After
Order looks random	Order by most recent updated

How to set up and validate locally

For user_id 1, we need to populate the list of Contributed Projects. Create some Events and update the related Project.last_activity_at value:

current_user = User.find(1)

Issue.all.shuffle.each do |i|
  Event.create(project: i.project, target: i, action: 'commented', author: current_user)
  # Project update time: random hours ago
  last_activity = rand(1..50)
  i.project.touch(:last_activity_at, time: last_activity.hours.ago)
end

Visit Contributed Projects

• On master branch, the list order seems random (it is ordered by project id).
• On this branch, the list is ordered by last_activity_at

Database-related changes

The ContributedProjectsFinder was using an hard-coded ORDER BY projects.id DESC. This order can now be configured but will still default to ORDER BY projects.id DESC.

The only code path that is now using a different order is the contributed projects list. There, we have the very same query but we use ORDER BY project.last_activity_at DESC.

Query plan for current master query:

 Sort  (cost=3161.02..3161.30 rows=113 width=824) (actual time=1113.838..1113.849 rows=32 loops=1)
   Sort Key: projects.id DESC
   Sort Method: quicksort  Memory: 58kB
   Buffers: shared hit=2401 read=449 dirtied=15
   I/O Timings: read=1084.873 write=0.000
   ->  Nested Loop  (cost=1.14..3157.17 rows=113 width=824) (actual time=6.182..1110.633 rows=32 loops=1)
         Buffers: shared hit=2398 read=449 dirtied=15
         I/O Timings: read=1084.873 write=0.000
         ->  Unique  (cost=0.70..120.78 rows=883 width=4) (actual time=6.082..369.851 rows=103 loops=1)
               Buffers: shared hit=2212 read=291 dirtied=7
               I/O Timings: read=352.617 write=0.000
               ->  Index Only Scan using index_events_author_id_project_id_action_target_type_created_at on public.events  (cost=0.70..118.57 rows=884 width=4) (actual time=6.079..369.094 rows=3123 loops=1)
                     Index Cond: ((events.author_id = 116) AND (events.created_at >= '2023-02-14 09:40:46.939399+00'::timestamp with time zone))
                     Heap Fetches: 14
                     Filter: ((events.action = ANY ('{5,6}'::integer[])) OR (((events.target_type)::text = ANY ('{MergeRequest,Issue,WorkItem}'::text[])) AND (events.action = ANY ('{1,3,7,12}'::integer[]))))
                     Rows Removed by Filter: 72
                     Buffers: shared hit=2212 read=291 dirtied=7
                     I/O Timings: read=352.617 write=0.000
         ->  Index Scan using index_projects_on_id_partial_for_visibility on public.projects  (cost=0.43..3.43 rows=1 width=824) (actual time=7.183..7.183 rows=0 loops=103)
               Index Cond: (projects.id = events.project_id)
               Filter: ((projects.marked_for_deletion_at IS NULL) AND (NOT projects.pending_delete) AND (projects.visibility_level = 20))
               Rows Removed by Filter: 0
               Buffers: shared hit=182 read=158 dirtied=4
               I/O Timings: read=732.257 write=0.000

Query plan for this branch (with different order by clause):

 Sort  (cost=3161.02..3161.30 rows=113 width=824) (actual time=9.323..9.328 rows=32 loops=1)
   Sort Key: projects.last_activity_at DESC
   Sort Method: quicksort  Memory: 58kB
   Buffers: shared hit=2844
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=1.14..3157.17 rows=113 width=824) (actual time=0.199..9.127 rows=32 loops=1)
         Buffers: shared hit=2841
         I/O Timings: read=0.000 write=0.000
         ->  Unique  (cost=0.70..120.78 rows=883 width=4) (actual time=0.124..8.023 rows=103 loops=1)
               Buffers: shared hit=2503
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_events_author_id_project_id_action_target_type_created_at on public.events  (cost=0.70..118.57 rows=884 width=4) (actual time=0.123..7.739 rows=3123 loops=1)
                     Index Cond: ((events.author_id = 116) AND (events.created_at >= '2023-02-14 09:40:46.939399+00'::timestamp with time zone))
                     Heap Fetches: 14
                     Filter: ((events.action = ANY ('{5,6}'::integer[])) OR (((events.target_type)::text = ANY ('{MergeRequest,Issue,WorkItem}'::text[])) AND (events.action = ANY ('{1,3,7,12}'::integer[]))))
                     Rows Removed by Filter: 72
                     Buffers: shared hit=2503
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_projects_on_id_partial_for_visibility on public.projects  (cost=0.43..3.43 rows=1 width=824) (actual time=0.010..0.010 rows=0 loops=103)
               Index Cond: (projects.id = events.project_id)
               Filter: ((projects.marked_for_deletion_at IS NULL) AND (NOT projects.pending_delete) AND (projects.visibility_level = 20))
               Rows Removed by Filter: 0
               Buffers: shared hit=338
               I/O Timings: read=0.000 write=0.000