Group Search sort based on algorithmic score

The Group Search dropdown currently sorts by namespace.name, then namespace.id. A new sort should be added within the search method inside app/models/namespace.rb. That sort should use an algorithm to make sure that:

1. Exact search matches show first
2. Top level groups weighted higher than sub groups based on search term

Some areas to look for inspiration:

• The user search has an example that could be a useful starting point: app/models/user.rb
• The project model contains a sorted_by_similarity_desc scope which could be a cool way to do this: app/models/project.rb

current SQL

/*application:web,correlation_id:01F7KW07CJ0W82358TE9HRPGJS,endpoint_id:GET /api/:version/groups*/
WITH RECURSIVE "base_and_ancestors" AS (
(
        SELECT
            "namespaces".*
        FROM (( WITH "direct_groups" AS MATERIALIZED (
                    SELECT
                        "namespaces".*
                    FROM (
                        (
                            SELECT
                                "namespaces".*
                            FROM
                                "namespaces"
                                INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                            WHERE
                                "members"."type" = 'GroupMember'
                                AND "members"."source_type" = 'Namespace'
                                AND "namespaces"."type" = 'Group'
                                AND "members"."user_id" = 5708766
                                AND "members"."requested_at" IS NULL
                                AND (
                                    access_level >= 10
)
)
                        UNION (
                            SELECT
                                namespaces.*
                            FROM
                                "projects"
                                INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                                INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
                            WHERE
                                "project_authorizations"."user_id" = 5708766
)
) namespaces
                    WHERE
                        "namespaces"."type" = 'Group'
)
                    SELECT
                        "namespaces".*
                    FROM (
                        (
                            SELECT
                                "namespaces".*
                            FROM
                                "direct_groups" "namespaces"
                            WHERE
                                "namespaces"."type" = 'Group'
)
                        UNION (
                            SELECT
                                "namespaces".*
                            FROM
                                "namespaces"
                                INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
                            WHERE
                                "namespaces"."type" = 'Group'
                                AND "group_group_links"."shared_with_group_id" IN (
                                    SELECT
                                        "namespaces"."id"
                                    FROM
                                        "direct_groups" "namespaces"
                                    WHERE
                                        "namespaces"."type" = 'Group'
)
)
) namespaces
                        WHERE
                            "namespaces"."type" = 'Group'
)
                    UNION (
                        SELECT
                            "namespaces".*
                        FROM
                            "namespaces"
                            INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                        WHERE
                            "members"."type" = 'GroupMember'
                            AND "members"."source_type" = 'Namespace'
                            AND "namespaces"."type" = 'Group'
                            AND "members"."user_id" = 5708766
                            AND "members"."access_level" = 5
                            AND (
                                EXISTS (
                                    SELECT
                                        1
                                    FROM
                                        "plans"
                                        INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id"
                                    WHERE
                                        "plans"."name" IN (
                                            'silver', 'premium', 'premium_trial', 'gold', 'ultimate', 'ultimate_trial'
)
                                        AND (
                                            gitlab_subscriptions.namespace_id = namespaces.id
)
)
)
)
) namespaces
                    WHERE
                        "namespaces"."type" = 'Group'
)
                UNION (
                    SELECT
                        "namespaces".*
                    FROM
                        "namespaces",
                        "base_and_ancestors"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND "namespaces"."id" = "base_and_ancestors"."parent_id"
)
),
            "base_and_descendants" AS (
(
                    SELECT
                        "namespaces".*
                    FROM
                        "namespaces"
                        INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                    WHERE
                        "members"."type" = 'GroupMember'
                        AND "members"."source_type" = 'Namespace'
                        AND "namespaces"."type" = 'Group'
                        AND "members"."user_id" = 5708766
                        AND "members"."requested_at" IS NULL
                        AND (access_level >= 10))
                UNION (
                    SELECT
                        "namespaces".*
                    FROM
                        "namespaces",
                        "base_and_descendants"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND "namespaces"."parent_id" = "base_and_descendants"."id"))
        SELECT
            "namespaces".*
        FROM ((
                SELECT
                    "namespaces".*
                FROM
                    "base_and_ancestors" AS "namespaces"
                WHERE
                    "namespaces"."type" = 'Group')
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "base_and_descendants" AS "namespaces"
                WHERE
                    "namespaces"."type" = 'Group')) namespaces
    WHERE
        "namespaces"."type" = 'Group'
        AND "namespaces"."id" IN (
            SELECT
                "routes"."source_id"
            FROM
                "routes"
            WHERE
                "routes"."source_type" = 'Namespace'
                AND ("routes"."path" ILIKE '%gitlab%'
                    OR "routes"."name" ILIKE '%gitlab%'))
    ORDER BY
        "namespaces"."name" ASC,
        "namespaces"."id" ASC
    LIMIT 20 OFFSET 0

Plan

1. Adjust the Groups API endpoint GET /groups to allow sorting by similarity where exact matches and top level groups are given priority
2. Change the frontend Groups API drop down call used in the Search page to send in the new params from the previous step

Release Notes