Update project overrides query
What does this MR do?
After introducing group-level integration, we have to exclude them from the query.
Basically, this excludes the possible groups with custom integration retrieved in the subquery. So, we fix a possible bug where pagination is not working as expected because some of the services could be group services that are going to be excluded from the query.
Follow-up from !33043 (merged) and !33838 (merged). Project.with_custom_integration_for
is not used at the moment, the frontend is still work-in-progress. No user-facing change as there is no change to show.
Migration
We add an index to improve the query performance:
CREATE INDEX index_services_on_project_and_type_where_inherit_null ON services USING btree (project_id, type) WHERE (inherit_from_id IS NULL);
rails db:migrate
$ rails db:migrate
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: migrating =
-- transaction_open?()
-> 0.0000s
-- index_exists?(:services, [:project_id, :type], {:where=>"inherit_from_id IS NULL", :name=>"index_services_on_project_and_type_where_inherit_null", :algorithm=>:concurrently})
-> 0.0053s
-- execute("SET statement_timeout TO 0")
-> 0.0008s
-- add_index(:services, [:project_id, :type], {:where=>"inherit_from_id IS NULL", :name=>"index_services_on_project_and_type_where_inherit_null", :algorithm=>:concurrently})
-> 0.0099s
-- execute("RESET ALL")
-> 0.0007s
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: migrated (0.0178s)
rails db:migrate:down VERSION=20210414095944
$ rails db:migrate:down VERSION=20210414095944
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: reverting =
-- transaction_open?()
-> 0.0000s
-- indexes(:services)
-> 0.0051s
-- execute("SET statement_timeout TO 0")
-> 0.0006s
-- remove_index(:services, {:algorithm=>:concurrently, :name=>"index_services_on_project_and_type_where_inherit_null"})
-> 0.0064s
-- execute("RESET ALL")
-> 0.0007s
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: reverted (0.0139s)
Query
SELECT "projects".*
FROM "projects"
WHERE "projects"."id" IN
(SELECT "services"."project_id"
FROM "services"
WHERE "services"."type" = 'JiraService'
AND "services"."inherit_from_id" IS NULL
AND "services"."project_id" IS NOT NULL
LIMIT 20
OFFSET 480)
Time: 90.220 ms
- planning: 6.028 ms
- execution: 84.192 ms
- I/O read: 78.714 ms
- I/O write: N/A
https://console.postgres.ai/shared/e053a4fa-bee1-40ce-ba0b-0febc8a757eb
Related to #218252 (closed)