Skip to content

Instantly share code, notes, and snippets.

@sauloperez
Last active June 5, 2021 08:21
Show Gist options
  • Save sauloperez/6db056f8f0243c2d5e1ac2364f762577 to your computer and use it in GitHub Desktop.
Save sauloperez/6db056f8f0243c2d5e1ac2364f762577 to your computer and use it in GitHub Desktop.
Gotta love PostgreSQL
-- questions + count of its delegated votes
SELECT
questions_with_delegation_votes_count.*,
CASE
WHEN has_granter THEN count_by_setting_id
ELSE 0
END AS total_delegates
FROM (
SELECT
decidim_consultations_questions.*,
COALESCE(CAST(granter_id > 0 AS BOOLEAN), FALSE) AS has_granter,
COUNT(*) OVER (
PARTITION BY decidim_action_delegator_settings.id
) AS count_by_setting_id
FROM "decidim_consultations_questions"
LEFT OUTER JOIN "decidim_consultations_votes"
ON "decidim_consultations_votes"."decidim_consultation_question_id" = "decidim_consultations_questions"."id"
LEFT JOIN "decidim_action_delegator_delegations"
ON "decidim_consultations_votes"."decidim_author_id" = "decidim_action_delegator_delegations"."granter_id"
LEFT JOIN "decidim_action_delegator_settings"
ON "decidim_action_delegator_settings"."id" = "decidim_action_delegator_delegations"."decidim_action_delegator_setting_id"
LEFT JOIN "decidim_consultations"
ON "decidim_consultations"."id" = "decidim_action_delegator_settings"."decidim_consultation_id"
WHERE "decidim_consultations_questions"."decidim_consultation_id" = 13
AND "decidim_consultations_questions"."published_at" IS NOT NULL
ORDER BY "decidim_consultations_questions"."order" ASC
) AS questions_with_delegation_votes_count
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment