Add a count for recently added group members
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 msShared 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