Decidim metrics queries

queries.sql

/* 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 */