Public
Authored by Andreas Brandl 🚀

milestone finder query

Edited
query.sql 4.66 KB
  • In !25230 (merged), the OR "milestones"."group_id" IN (...) part is now:

    WITH RECURSIVE "base_and_ancestors" AS
      (SELECT "namespaces".*
       FROM
         (SELECT "namespaces".*
          FROM "namespaces"
          INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
          WHERE "members"."source_type" = 'Namespace'
            AND "namespaces"."type" IN ('Group')
            AND "members"."user_id" = 3245056
            AND "members"."type" IN ('GroupMember')
            AND "members"."requested_at" IS NULL
          UNION SELECT namespaces.*
          FROM "projects"
          INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
          INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
          WHERE "project_authorizations"."user_id" = 3245056) namespaces
       WHERE "namespaces"."type" IN ('Group')
       UNION SELECT "namespaces".*
       FROM "namespaces",
            "base_and_ancestors"
       WHERE "namespaces"."type" IN ('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"."source_type" = 'Namespace'
         AND "namespaces"."type" IN ('Group')
         AND "members"."user_id" = 3245056
         AND "members"."type" IN ('GroupMember')
         AND "members"."requested_at" IS NULL
       UNION SELECT "namespaces".*
       FROM "namespaces",
            "base_and_descendants"
       WHERE "namespaces"."type" IN ('Group')
         AND "namespaces"."parent_id" = "base_and_descendants"."id")
    SELECT "id"
    FROM
      (SELECT "namespaces".*
       FROM "base_and_ancestors" AS "namespaces"
       WHERE "namespaces"."type" IN ('Group')
       UNION SELECT "namespaces".*
       FROM "base_and_descendants" AS "namespaces"
       WHERE "namespaces"."type" IN ('Group')) namespaces
    WHERE "namespaces"."type" IN ('Group')
    ORDER BY "namespaces"."id" DESC)
  • SELECT
        COUNT(*) AS count_all,
        "milestones"."state" AS milestones_state
    FROM
        "milestones"
    WHERE ("milestones"."project_id" IN (
            SELECT
                "projects"."id"
            FROM
                "projects"
                INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
            WHERE
                "project_authorizations"."user_id" = 3245056
                AND "projects"."archived" = 'f'
            ORDER BY
                GREATEST (COALESCE(last_activity_at, '1970-01-01'),
                    COALESCE(last_repository_updated_at, '1970-01-01'))
                DESC)
            OR "milestones"."group_id" IN (
    
    
              WITH RECURSIVE "base_and_ancestors" AS
                (SELECT "namespaces".*
                 FROM
                   (SELECT "namespaces".*
                    FROM "namespaces"
                    INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                    WHERE "members"."source_type" = 'Namespace'
                      AND "namespaces"."type" IN ('Group')
                      AND "members"."user_id" = 3245056
                      AND "members"."type" IN ('GroupMember')
                      AND "members"."requested_at" IS NULL
                    UNION SELECT namespaces.*
                    FROM "projects"
                    INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
                    INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                    WHERE "project_authorizations"."user_id" = 3245056) namespaces
                 WHERE "namespaces"."type" IN ('Group')
                 UNION SELECT "namespaces".*
                 FROM "namespaces",
                      "base_and_ancestors"
                 WHERE "namespaces"."type" IN ('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"."source_type" = 'Namespace'
                   AND "namespaces"."type" IN ('Group')
                   AND "members"."user_id" = 3245056
                   AND "members"."type" IN ('GroupMember')
                   AND "members"."requested_at" IS NULL
                 UNION SELECT "namespaces".*
                 FROM "namespaces",
                      "base_and_descendants"
                 WHERE "namespaces"."type" IN ('Group')
                   AND "namespaces"."parent_id" = "base_and_descendants"."id")
              SELECT "id"
              FROM
                (SELECT "namespaces".*
                 FROM "base_and_ancestors" AS "namespaces"
                 WHERE "namespaces"."type" IN ('Group')
                 UNION SELECT "namespaces".*
                 FROM "base_and_descendants" AS "namespaces"
                 WHERE "namespaces"."type" IN ('Group')) namespaces
              WHERE "namespaces"."type" IN ('Group')
              ORDER BY "namespaces"."id" DESC
    
    
            )
    )
    GROUP BY
        "milestones"."state"
    Edited by Nikolay Samokhvalov
  • the new version, full query. Analysis/discussion: !25230 (comment 142154333)

    Edited by Nikolay Samokhvalov
Markdown is supported
0% or
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!
Please register or to comment