Skip to content

Add a count for recently added group members

Aakriti Gupta requested to merge ag-add-stats-for-new-recent-members into master

What does this MR do?

This MR is one of the many MRs to build the backend for Group Level Activity Analytics MVC

This MR adds a count for recently added group members, but only those that are visible for the user.

Database review notes

SQL
SELECT
    COUNT(*)
FROM ((
        SELECT
            "members".*
        FROM
            "members"
        WHERE
            "members"."type" = 'GroupMember'
            AND "members"."source_id" = 9970
            AND "members"."source_type" = 'Namespace'
            AND "members"."requested_at" IS NULL)
    UNION (
        SELECT
            "members".*
        FROM
            "members"
        WHERE
            "members"."type" = 'GroupMember'
            AND "members"."source_type" = 'Namespace'
            AND "members"."requested_at" IS NULL
            AND "members"."source_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
                        SELECT
                            "namespaces".*
                        FROM
                            "namespaces"
                        WHERE
                            "namespaces"."type" = 'Group'
                            AND "namespaces"."parent_id" = 9970)
                    UNION (
                        SELECT
                            "namespaces".*
                        FROM
                            "namespaces",
                            "base_and_descendants"
                        WHERE
                            "namespaces"."type" = 'Group'
                            AND "namespaces"."parent_id" = "base_and_descendants"."id"))
                SELECT
                    "id"
                FROM
                    "base_and_descendants" AS "namespaces")
                AND "members"."user_id" NOT IN (
                    SELECT
                        "users"."id"
                    FROM
                        "users"
                        INNER JOIN "members" ON "users"."id" = "members"."user_id"
                    WHERE
                        "members"."type" = 'GroupMember'
                        AND "members"."source_type" = 'Namespace'
                        AND "members"."source_id" = 9970
                        AND "members"."source_type" = 'Namespace'
                        AND "members"."requested_at" IS NULL))) members
WHERE
    "members"."type" = 'GroupMember'
    AND "members"."source_type" = 'Namespace'
    AND "members"."created_at" >= '2019-12-26 12:35:40.208911'

Link to query run in #database-lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1585144351110500

Query plan: https://explain.depesz.com/s/7Fl6

Summary ``` Time: 15.042 ms - planning: 1.972 ms - execution: 13.070 ms - I/O read: 0.000 ms - I/O write: 0.000 ms

Shared buffers:

  • hits: 8467 (~66.10 MiB) from the buffer pool
  • reads: 0 from the OS file cache, including disk I/O
  • dirtied: 0
  • writes: 0
  </details>

<!--

Describe in detail what your merge request does and why.

Are there any risks involved with the proposed change? What additional
test coverage is introduced to offset the risk?

Please keep this description up-to-date with any discussion that takes
place so that reviewers can understand your intent. This is especially
important if they didn't participate in the discussion.

-->

## Screenshots

<!-- Please include any relevant screenshots that will assist reviewers and future readers -->

## Does this MR meet the acceptance criteria?

### Conformity

- [-] [Changelog entry](https://docs.gitlab.com/ee/development/changelog.html) 
- [x] [Documentation](https://docs.gitlab.com/ee/development/documentation/workflow.html) ([if required](https://docs.gitlab.com/ee/development/documentation/workflow.html#when-documentation-is-required))
- [x] [Code review guidelines](https://docs.gitlab.com/ee/development/code_review.html)
- [x] [Merge request performance guidelines](https://docs.gitlab.com/ee/development/merge_request_performance_guidelines.html)
- [x] [Style guides](https://gitlab.com/gitlab-org/gitlab-ee/blob/master/doc/development/contributing/style_guides.md)
- [ ] [Database guides](https://docs.gitlab.com/ee/development/README.html#database-guides)
- [x] [Separation of EE specific content](https://docs.gitlab.com/ee/development/ee_features.html#separation-of-ee-code)

### Availability and Testing

<!-- What risks does this change pose? How might it affect the quality/performance of the product?
What additional test coverage or changes to tests will be needed?
Will it require cross-browser testing?
See the test engineering process for further guidelines: https://about.gitlab.com/handbook/engineering/quality/test-engineering/ -->

<!-- If cross-browser testing is not required, please remove the relevant item, or mark it as not needed: [-] -->

- [-] [Review and add/update tests for this feature/bug](https://docs.gitlab.com/ee/development/testing_guide/index.html). Consider [all test levels](https://docs.gitlab.com/ee/development/testing_guide/testing_levels.html). See the [Test Planning Process](https://about.gitlab.com/handbook/engineering/quality/test-engineering).
- [-] [Tested in all supported browsers](https://docs.gitlab.com/ee/install/requirements.html#supported-web-browsers)
- [-] Informed Infrastructure department of a default or new setting change, if applicable per [definition of done](https://docs.gitlab.com/ee/development/contributing/merge_request_workflow.html#definition-of-done)

### Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in [the security review guidelines](https://about.gitlab.com/handbook/engineering/security/#when-to-request-a-security-review):

- [-] 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
Edited by Aakriti Gupta

Merge request reports