1 files changed, 39 insertions, 20 deletions

diff --git a/app/models/group.rb b/app/models/group.rb
index 04cb6b8b4da..dd7624ab420 100644
--- a/app/models/group.rb
+++ b/app/models/group.rb
@@ -325,15 +325,17 @@ class Group < Namespace
   def members_with_parents
     # Avoids an unnecessary SELECT when the group has no parents
     source_ids =
-      if parent_id
+      if has_parent?
         self_and_ancestors.reorder(nil).select(:id)
       else
         id
       end
 
-    GroupMember
-      .active_without_invites_and_requests
-      .where(source_id: source_ids)
+    group_hierarchy_members = GroupMember.active_without_invites_and_requests
+                                         .where(source_id: source_ids)
+
+    GroupMember.from_union([group_hierarchy_members,
+                            members_from_self_and_ancestor_group_shares])
   end
 
   def members_from_self_and_ancestors_with_effective_access_level
@@ -398,7 +400,7 @@ class Group < Namespace
                                             .first
                                             &.access_level
 
-    max_member_access || max_member_access_for_user_from_shared_groups(user) || GroupMember::NO_ACCESS
+    max_member_access || GroupMember::NO_ACCESS
   end
 
   def mattermost_team_params
@@ -494,6 +496,11 @@ class Group < Namespace
     # TODO: group hooks https://gitlab.com/gitlab-org/gitlab/-/issues/216904
   end
 
+  def preload_shared_group_links
+    preloader = ActiveRecord::Associations::Preloader.new
+    preloader.preload(self, shared_with_group_links: [shared_with_group: :route])
+  end
+
   private
 
   def update_two_factor_requirement
@@ -524,27 +531,39 @@ class Group < Namespace
     errors.add(:visibility_level, "#{visibility} is not allowed since there are sub-groups with higher visibility.")
   end
 
-  def max_member_access_for_user_from_shared_groups(user)
+  def members_from_self_and_ancestor_group_shares
     group_group_link_table = GroupGroupLink.arel_table
     group_member_table = GroupMember.arel_table
 
-    group_group_links_query = GroupGroupLink.where(shared_group_id: self_and_ancestors_ids)
+    source_ids =
+      if has_parent?
+        self_and_ancestors.reorder(nil).select(:id)
+      else
+        id
+      end
+
+    group_group_links_query = GroupGroupLink.where(shared_group_id: source_ids)
     cte = Gitlab::SQL::CTE.new(:group_group_links_cte, group_group_links_query)
     cte_alias = cte.table.alias(GroupGroupLink.table_name)
 
-    link = GroupGroupLink
-             .with(cte.to_arel)
-             .select(smallest_value_arel([cte_alias[:group_access], group_member_table[:access_level]],
-                                         'group_access'))
-             .from([group_member_table, cte.alias_to(group_group_link_table)])
-             .where(group_member_table[:user_id].eq(user.id))
-             .where(group_member_table[:requested_at].eq(nil))
-             .where(group_member_table[:source_id].eq(group_group_link_table[:shared_with_group_id]))
-             .where(group_member_table[:source_type].eq('Namespace'))
-             .reorder(Arel::Nodes::Descending.new(group_group_link_table[:group_access]))
-             .first
-
-    link&.group_access
+    # Instead of members.access_level, we need to maximize that access_level at
+    # the respective group_group_links.group_access.
+    member_columns = GroupMember.attribute_names.map do |column_name|
+      if column_name == 'access_level'
+        smallest_value_arel([cte_alias[:group_access], group_member_table[:access_level]],
+                            'access_level')
+      else
+        group_member_table[column_name]
+      end
+    end
+
+    GroupMember
+      .with(cte.to_arel)
+      .select(*member_columns)
+      .from([group_member_table, cte.alias_to(group_group_link_table)])
+      .where(group_member_table[:requested_at].eq(nil))
+      .where(group_member_table[:source_id].eq(group_group_link_table[:shared_with_group_id]))
+      .where(group_member_table[:source_type].eq('Namespace'))
   end
 
   def smallest_value_arel(args, column_alias)