System Information
- Strapi Version: 3.5.1
Hi,
In strapi-connector-bookshelf there’s the following code:
if (_.has(filters, ‘where’) && Array.isArray(filters.where) && filters.where.length > 0) {
qb.distinct();
}
Can someone please explain to me the reason for it?
To me it looks pretty much useless to always add distinct if there’s a where, it makes the database really inefficient when having semi-large data sets and it’s not possible to add a couple of indices since it’s doing distinct on *.
Here’s an example of all distinct queries when starting strapi:
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_documentation_config’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_upload_settings’ and “core_store”.“environment” = ‘development’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_users-permissions_grant’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_users-permissions_email’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_users-permissions_advanced’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::application::tag.tag’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
… all content types like above
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::strapi::permission’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::strapi::role’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::strapi::user’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::plugins::upload.file’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::plugins::users-permissions.permission’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::plugins::users-permissions.role’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘plugin_content_manager_configuration_content_types::plugins::users-permissions.user’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “strapi_permission” where 1 = 0
select distinct * from “strapi_permission” where 1 = 0
select distinct * from “strapi_role” where “strapi_role”.“code” = ‘strapi-editor’ limit 1
select distinct * from “strapi_permission” left join “strapi”.“public”.“strapi_role” as “strapi_role_1” on “strapi_role_1”.“id” = “strapi_permission”.“role” where “strapi_permission”.“subject” = ‘application::exam.exam’ and “strapi_permission”.“action” in (‘plugins::content-manager.explorer.read’, ‘plugins::content-manager.explorer.update’, ‘plugins::content-manager.explorer.create’, ‘plugins::content-manager.explorer.delete’) and “strapi_role_1”.“id” = 7 limit 100
select distinct * from “strapi_permission” left join “strapi”.“public”.“strapi_role” as “strapi_role_1” on “strapi_role_1”.“id” = “strapi_permission”.“role” where “strapi_permission”.“subject” = ‘application::ext-ref.ext-ref’ and “strapi_permission”.“action” in (‘plugins::content-manager.explorer.read’, ‘plugins::content-manager.explorer.create’, ‘plugins::content-manager.explorer.update’, ‘plugins::content-manager.explorer.delete’, ‘plugins::content-manager.explorer.publish’) and “strapi_role_1”.“id” = 7 limit 100
select distinct * from “strapi_role” where “strapi_role”.“code” = ‘strapi-super-admin’ limit 1
select distinct * from “strapi_role” where “strapi_role”.“code” = ‘strapi-super-admin’ limit 1
select distinct * from “strapi_permission” left join “strapi”.“public”.“strapi_role” as “strapi_role_1” on “strapi_role_1”.“id” = “strapi_permission”.“role” where “strapi_role_1”.“id” = 6
select distinct * from “strapi_role” where “strapi_role”.“code” = ‘strapi-super-admin’ limit 1
select distinct * from “strapi_administrator” left join “strapi”.“public”.“strapi_users_roles” as “strapi_users_roles_1” on “strapi_users_roles_1”.“user_id” = “strapi_administrator”.“id” left join “strapi”.“public”.“strapi_role” as “strapi_role_1” on “strapi_users_roles_1”.“role_id” = “strapi_role_1”.“id” where ((“strapi_role_1”.“id” = 6))
select distinct * from “strapi_administrator” left join “strapi”.“public”.“strapi_users_roles” as “strapi_users_roles_1” on “strapi_users_roles_1”.“user_id” = “strapi_administrator”.“id” left join “strapi”.“public”.“strapi_role” as “strapi_role_1” on “strapi_users_roles_1”.“role_id” = “strapi_role_1”.“id” where “strapi_role_1”.“id” is null
select distinct * from “core_store” where “core_store”.“key” = ‘core_admin_auth’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“key” = ‘core_admin_auth’ and “core_store”.“environment” = ‘’ and “core_store”.“tag” = ‘’ limit 1
select distinct * from “core_store” where “core_store”.“id” = 58 limit 1
When I edit a content type it again does a bunch of distincts (even when searching by id):
select distinct * from “strapi_administrator” where “strapi_administrator”.“id” = 3 limit 1
select distinct * from “strapi_permission” left join “strapi”.“public”.“strapi_role” as “strapi_role_1” on “strapi_role_1”.“id” = “strapi_permission”.“role” where “strapi_role_1”.“id” in (6)
select distinct * from “tags” where “tags”.“id” = ‘1’ limit 1
select distinct * from “strapi_role” left join “strapi”.“public”.“strapi_users_roles” as “strapi_users_roles_1” on “strapi_users_roles_1”.“role_id” = “strapi_role”.“id” left join “strapi”.“public”.“strapi_administrator” as “strapi_administrator_1” on “strapi_users_roles_1”.“user_id” = “strapi_administrator_1”.“id” where “strapi_administrator_1”.“id” = 3 limit 100
For the upload plugin, it seems like it doesn’t use that if statement but it’s also slapping on distinct:
select distinct “upload_file”.*, “upload_file”.“updated_at” as “_strapi_tmp_upload_file_updated_at” from “upload_file” where (“upload_file”.“name”::text ILIKE ? or “upload_file”.“id”::text ILIKE ?) order by “_strapi_tmp_upload_file_updated_at” desc limit ?
Again, making it impossible to index the query.
This is really getting problematic when having a larger data set.
Is there a reason for the if-statement at the top? And does anyone know where the upload_file distinct gets added? The only place containing distinct is packages/strapi-connector-bookshelf/lib/buildQuery.js and that one isn’t responsible for doing the distinct "upload_file".*