Skip to content

Instantly share code, notes, and snippets.

@amiedes
Created Feb 13, 2019
Embed
What would you like to do?
Decidim metrics queries
/* This query is generated by the development_app and it's OK */
SELECT Count(*) AS count_all,
decidim_categorizations.decidim_category_id AS
decidim_categorizations_decidim_category_id,
"participatory_space_type" AS participatory_space_type,
"participatory_space_id" AS participatory_space_id,
decidim_components.id AS decidim_components_id
FROM "decidim_accountability_results"
INNER JOIN "decidim_components"
ON "decidim_components"."id" =
"decidim_accountability_results"."decidim_component_id"
LEFT OUTER JOIN "decidim_categorizations"
ON "decidim_categorizations"."categorizable_id" =
"decidim_accountability_results"."id"
AND "decidim_categorizations"."categorizable_type" = $1
LEFT OUTER JOIN "decidim_categories"
ON "decidim_categories"."id" =
"decidim_categorizations"."decidim_category_id"
WHERE "decidim_accountability_results"."decidim_component_id" IN (SELECT
"decidim_components"."id"
FROM
"decidim_components"
WHERE
( ( ( (
"decidim_components"."participatory_space_type" = $2
AND "decidim_components"."participatory_space_id" IN ( $3, $4 )
OR "decidim_components"."participatory_space_type" = $5
AND "decidim_components"."participatory_space_id" IN
( $6, $7, $8, $9 )
)
OR
"decidim_components"."participatory_space_type" = $10
AND "decidim_components"."participatory_space_id" IN ( $11,
$12 ) )
OR
"decidim_components"."participatory_space_type" = $13
AND "decidim_components"."participatory_space_id" IN (
$14, $15, $16 ) )
OR
"decidim_components"."participatory_space_type" = $17
AND "decidim_components"."participatory_space_id" IN (
$18, $19, $20, $21,
$22, $23 ) )
AND
"decidim_components"."published_at" IS NOT NULL
ORDER BY
"decidim_components"."weight" ASC,
"decidim_components"."manifest_name" ASC)
AND ( decidim_accountability_results.created_at <=
'2019-02-12 23:59:59.999999' )
GROUP BY decidim_categorizations.decidim_category_id,
"participatory_space_type",
"participatory_space_id",
decidim_components.id
/*
This query is generated by our application and it breaks with the following error:
Error performing Decidim::MetricJob (Job ID: d6cc3ec9-9a72-479d-8a6c-6ca54505b1fc) from DelayedJob(metrics) in 3976.15ms:
ActiveRecord::StatementInvalid (PG::GroupingError: ERROR: column "decidim_accountability_results.id" must appear in the GROUP BY clause or be used in an aggregate function
*/
SELECT Count(*) AS count_all,
decidim_categorizations.decidim_category_id AS
decidim_categorizations_decidim_category_id,
"participatory_space_type" AS participatory_space_type,
"participatory_space_id" AS participatory_space_id,
decidim_components.id AS decidim_components_id
FROM "decidim_accountability_results"
INNER JOIN "decidim_components"
ON "decidim_components"."id" =
"decidim_accountability_results"."decidim_component_id"
LEFT OUTER JOIN "decidim_categorizations"
ON "decidim_categorizations"."categorizable_id" =
"decidim_accountability_results"."id"
AND "decidim_categorizations"."categorizable_type" = $1
LEFT OUTER JOIN "decidim_categories"
ON "decidim_categories"."id" =
"decidim_categorizations"."decidim_category_id"
WHERE "decidim_accountability_results"."decidim_component_id" IN (SELECT
"decidim_components"."id"
FROM
"decidim_components"
WHERE
"decidim_components"."participatory_space_type" = $2
AND "decidim_components"."participatory_space_id" IN (
$3, $4, $5, $6 )
AND "decidim_components"."published_at" IS NOT NULL
ORDER BY
"decidim_components"."weight" ASC,
"decidim_components"."manifest_name" ASC)
AND ( decidim_accountability_results.created_at <=
'2019-02-12 23:59:59.999999' )
GROUP BY decidim_categorizations.decidim_category_id,
"participatory_space_type",
"participatory_space_id",
decidim_components.id
ORDER BY "decidim_accountability_results"."id" ASC /* This ORDER BY does not appear in the development_app query I have not been able to determine why it's appended on our case */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment