Backend support for advanced search multi-select project [RUN ALL RSPEC] [RUN AS-IF-FOSS]

What does this MR do?

Related to Epic: &6110 and Issue: #332631

Backend support for allowing multi-select project search in Advanced Search only. Basic Search support will be handled in a separate MR. This feature is behind a disabled by default feature flag: advanced_search_multi_project_select for groups

Added a new param project_ids which will take the form of project_ids=1,2,3 or project_ids=1 and is only set if a group is selected and the Feature Flag is enabled for the selected group.

If any of the projects selected have Advanced Search enabled, the query will go to Elasticsearch. Once Basic Search supports multi-select projects, additional work will be done to combine results when projects results come from Postgres and Elasticsearch.

How to test

Note: You need to have Elasticsearch enabled in GDK, have Advanced Search enabled for search + indexing, and have created the index using rake commands or the Admin UI (rake gitlab:elastic:index)

1. Enable the feature for a specific group in the rails console: Feature.enable(:advanced_search_multi_project_select, group)
2. Navigate to the search page: http://localhost:3000/search
3. Select the group you enabled the feature for in the drop down
4. Take note of 2 project ids for that group
5. Manually add &project_ids=AA,BB to the URL where AA and BB are the project_ids from the previous step
6. perform some searches and make sure you are getting data back from both projects

Note: I also did some testing by turning on Namespace limiting and trying to send the project_ids parameter to groups and projects that only support Basic Search to make sure it didn't break anything.

Database

There is a count query and select query for each search scope and will detail both here. Project ids used: 278964 and 7764

Select

Explain plan: https://explain.depesz.com/s/PbPt

SELECT
    “users”. *
FROM
    “users”
WHERE (((“users”.“name” ILIKE ‘%test%’
            OR “users”.“username” ILIKE ‘%test%’)
            OR “users”.“email” = ‘test’)
            OR “users”.“id” = (
                SELECT
                    “emails”.“user_id”
                FROM
                    “emails”
                WHERE
                    “emails”.“email” = ‘test’
                LIMIT 1))
        AND “users”.“id” IN (
            SELECT
                “users”.“id”
            FROM
                “users”
                INNER JOIN “project_authorizations” ON “project_authorizations”.“user_id” = “users”.“id”
            WHERE
                “project_authorizations”.“project_id” IN (278964, 7764))
        ORDER BY
            CASE WHEN users.name = ‘test’ THEN
                0
            WHEN users.username = ‘test’ THEN
                1
            WHEN users.email = ‘test’ THEN
                2
            ELSE
                3
            END,
            “users”.“name” ASC
        LIMIT 20 OFFSET 0

Count

Explain plan: https://explain.depesz.com/s/HWrU

SELECT
    COUNT(*)
FROM (
    SELECT
        1 AS one
    FROM
        “users”
    WHERE (((“users”.“name” ILIKE ‘%test%’
                OR “users”.“username” ILIKE ‘%test%’)
                OR “users”.“email” = ‘test’)
                OR “users”.“id” = (
                    SELECT
                        “emails”.“user_id”
                    FROM
                        “emails”
                    WHERE
                        “emails”.“email” = ‘test’
                    LIMIT 1))
            AND “users”.“id” IN (
                SELECT
                    “users”.“id”
                FROM
                    “users”
                    INNER JOIN “project_authorizations” ON “project_authorizations”.“user_id” = “users”.“id”
                WHERE
                    “project_authorizations”.“project_id” IN (278964, 7764))
            LIMIT 100) subquery_for_count

Screenshots (strongly suggested)

Tested with two project ids which map to gnome-1 and Fdroidclient projects (which you can see in the screenshots)

code

issues

merge requests

milestones

comments

commits

wiki

users

Does this MR meet the acceptance criteria?

Conformity

• I have included a changelog entry, or it's not needed. (Does this MR need a changelog?)
• I have added/updated documentation, or it's not needed. (Is documentation required?)
• I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
• I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
• I have self-reviewed this MR per code review guidelines.
• This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
• I have followed the style guides.

Availability and Testing

• I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
• I have tested this MR in all supported browsers, or it's not needed.
• I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team