Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Optimising N+1 problem in PG with "json_agg"
WITH question_options_f AS
( SELECT id,
question_id,
name,
fixed,
nota,
name_html,
name_raw
FROM question_options
WHERE deleted_at IS NULL
ORDER BY ROW ASC),
questions_with_options AS
( SELECT questions.*,
to_char(questions.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS created_at,
to_char(questions.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS updated_at,
json_agg(options_q.*) AS question_options
FROM questions,
(SELECT *
FROM question_options_f) AS options_q
WHERE options_q.question_id = questions.id
GROUP BY questions.id),
text_nodes_of AS
( SELECT tn.*,
to_char(tn.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS created_at,
to_char(tn.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS updated_at
FROM text_nodes tn),
conditions_of AS
( SELECT inner_conditions.*,
json_build_object(inner_conditions.operator, inner_conditions.human_operator) AS
OPERATOR,
concat( row_to_json(row(inner_conditions.source))->'f1'->'name_raw'::varchar ,' ',inner_conditions.human_operator,' ' ,row_to_json(row(inner_conditions.operand))->'f1'->'name_raw'::varchar ) AS equation ,
json_build_array( row_to_json(row(inner_conditions.source))->'f1'->'name_raw'::varchar ,inner_conditions.human_operator::varchar ,row_to_json(row(inner_conditions.operand))->'f1'->'name_raw'::varchar ) AS equation_array
FROM
( SELECT conditions.id,
rule_id,
element_id,
element_type,
element_type AS TYPE,
uuid_element_id,
uuid_element_type,
coalesce(question_as_element.question_type) AS source_type,
coalesce(value_type) AS operand_type,
coalesce(question_as_element) AS SOURCE,
coalesce(question_options_as_target) AS operand,
OPERATOR,
(CASE
WHEN conditions.operator = 'eq' THEN 'equals'
WHEN
OPERATOR = 'neq' THEN 'does not equal'
ELSE 'X'
END) AS human_operator
FROM conditions
LEFT OUTER JOIN questions_with_options question_as_element ON (question_as_element.id = uuid_element_id
AND uuid_element_type = 'Question')
LEFT OUTER JOIN question_options question_options_as_target ON (question_options_as_target.id = uuid_target_id
AND uuid_target_type = 'QuestionOption')
ORDER BY conditions.row ASC ) AS inner_conditions),
rules_of AS
( SELECT r.id,
ROW,
r.element_type,
r.element_id,
( SELECT COALESCE(json_agg(condition.*) FILTER (
WHERE condition.id IS NOT NULL), '[]') AS conditions
FROM
(SELECT *
FROM conditions_of
WHERE conditions_of.rule_id = r.id) condition ) AS conditions
FROM rules r
GROUP BY r.id
ORDER BY r.row ASC),
nodes_of AS
( SELECT n.id,
n.block_id,
n.element_type,
n.element_type AS TYPE,
n.element_id,
(CASE
WHEN question_as_element IS NULL THEN row_to_json(text_node_as_element)
ELSE row_to_json(question_as_element)
END ) AS element,
( SELECT COALESCE(json_agg(rule.*) FILTER (
WHERE rule.id IS NOT NULL), '[]') AS rules
FROM
(SELECT *
FROM rules_of
WHERE rules_of.element_type = 'Node'
AND rules_of.element_id = n.id) RULE ) AS rules
FROM nodes n
LEFT OUTER JOIN text_nodes_of text_node_as_element ON (text_node_as_element.id = n.element_id
AND n.element_type = 'TextNode')
LEFT OUTER JOIN questions_with_options question_as_element ON (question_as_element.id = n.element_id
AND n.element_type != 'TextNode')
WHERE n.deleted_at IS NULL
ORDER BY n.row ASC),
blocks_of AS
( SELECT b.id,
b.list_id,
b.name,
b.row ,
( SELECT COALESCE(json_agg(rule.*) FILTER (
WHERE rule.id IS NOT NULL), '[]') AS rules
FROM
(SELECT *
FROM rules_of
WHERE rules_of.element_type = 'Block'
AND rules_of.element_id = b.id) RULE ) AS rules,
( SELECT json_agg(node.*) AS nodes
FROM
(SELECT *
FROM nodes_of
WHERE nodes_of.block_id = b.id) node ) AS nodes
FROM blocks b
WHERE b.deleted_at IS NULL
GROUP BY b.id
ORDER BY b.row ASC),
categories AS
( SELECT id,
name,
slug
FROM categories
WHERE deleted_at IS NULL )
SELECT row_to_json(results) AS list
FROM
( SELECT l.id,
l.slug,
l.row,
(SELECT CASE
WHEN l.state = 0 THEN 'draft'
WHEN l.state = 1 THEN 'active'
WHEN l.state = 2 THEN 'archived'
ELSE 'deleted'
END)::varchar AS state,
to_char(l.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS created_at,
to_char(l.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS updated_at,
l.has_random_order ,
( SELECT COALESCE(json_agg(block.*) FILTER (
WHERE block.id IS NOT NULL), '[]') AS blocks
FROM
(SELECT *
FROM blocks_of
WHERE blocks_of.list_id = l.id) BLOCK ) AS blocks ,
( SELECT COALESCE(json_agg(rule.*) FILTER (
WHERE rule.id IS NOT NULL), '[]') AS rules
FROM
(SELECT *
FROM rules_of
WHERE rules_of.element_type = 'List'
AND rules_of.element_id = l.id) RULE ) AS rules
FROM LISTS l
WHERE l.id = 61
AND l.state IN (0,
1,
2)
AND l.deleted_at IS NULL
GROUP BY l.id
LIMIT 1) AS results
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.