Follow-up from "Store mentioned users, groups, projects in DB using postgres array type"

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

The following discussion from !19088 (merged) should be addressed:

• @yorickpeterse started a discussion: (+1 comment)

  Please make sure an issue for this exists and is assigned, otherwise this comment may stick around for a long time.

We implemented storing mentions to DB tables in !19088 (merged), and because we are storing mentions in array columns we do not have foreign keys, so to ensure data integrity as well as permissions and access to the mentions when loading groups we need to check that mentioned groups are accessible for users.

Currently method that loads mentioned groups uses an expensive query. We need to run performance checks once we have production data before actually releasing group loading to production.

Check DB data for max mentioned groups per mentionable:

select issue_id, count(mentions_count.men_gr_id) gr_count from
(select DISTINCT unnest(mentioned_groups_ids) as men_gr_id, issue_id
from issue_user_mentions group by issue_id, mentioned_groups_ids) as mentions_count
group by mentions_count.issue_id order by gr_count desc limit 10

Code for loading groups that user has access to:

  def referenced_groups(current_user = nil)
    # TODO: IMPORTANT: Revisit before using it.
    # Check DB data for max mentioned groups per mentionable:
    #
    # select issue_id, count(mentions_count.men_gr_id) gr_count from
    # (select DISTINCT unnest(mentioned_groups_ids) as men_gr_id, issue_id
    # from issue_user_mentions group by issue_id, mentioned_groups_ids) as mentions_count
    # group by mentions_count.issue_id order by gr_count desc limit 10
    Group.where(id: user_mentions.select("unnest(mentioned_groups_ids)")).public_or_visible_to_user(current_user)
  end