Skip to content

Instantly share code, notes, and snippets.

@silenius
Created June 21, 2021 09:23
Show Gist options
  • Save silenius/5de95ceddd14e62bfc3e52366643bd32 to your computer and use it in GitHub Desktop.
Save silenius/5de95ceddd14e62bfc3e52366643bd32 to your computer and use it in GitHub Desktop.
WITH RECURSIVE parents(" %% (34442533520 anon)s", count_children, id, added, updated, effective, expiration, exclude_nav, inherits_parent_acl, weight, is_fts, props, content_type_id, owner_id, state_id, container_id, content_id, max_children, index_content_id, polymorphic_loading, rss, default_order, level) AS
(
SELECT
row_number() OVER (PARTITION BY content.container_id
ORDER BY
content.weight DESC) AS anon_1,
(
SELECT
count(*) AS count_1
FROM
content AS _count_children
WHERE
_count_children.container_id = content.id
)
AS count_children,
content.id AS id,
content.added AS added,
content.updated AS updated,
content.effective AS effective,
content.expiration AS expiration,
content.exclude_nav AS exclude_nav,
content.inherits_parent_acl AS inherits_parent_acl,
content.weight AS weight,
content.is_fts AS is_fts,
content.props AS props,
content.content_type_id AS content_type_id,
content.owner_id AS owner_id,
content.state_id AS state_id,
content.container_id AS container_id,
folder.content_id AS content_id,
folder.max_children AS max_children,
folder.index_content_id AS index_content_id,
folder.polymorphic_loading AS polymorphic_loading,
folder.rss AS rss,
folder.default_order AS default_order,
% (param_1)s AS level
FROM
content
JOIN
folder
ON folder.content_id = content.id
WHERE
content.exclude_nav = false
AND content.container_id = % (container_id_1)s
UNION ALL
SELECT
row_number() OVER (PARTITION BY content.container_id
ORDER BY
content.weight DESC) AS anon_1,
(
SELECT
count(*) AS count_1
FROM
content AS _count_children
WHERE
_count_children.container_id = content.id
)
AS count_children,
content.id AS content_id,
content.added AS content_added,
content.updated AS content_updated,
content.effective AS content_effective,
content.expiration AS content_expiration,
content.exclude_nav AS content_exclude_nav,
content.inherits_parent_acl AS content_inherits_parent_acl,
content.weight AS content_weight,
content.is_fts AS content_is_fts,
content.props AS content_props,
content.content_type_id AS content_content_type_id,
content.owner_id AS content_owner_id,
content.state_id AS content_state_id,
content.container_id AS content_container_id,
folder.content_id AS folder_content_id,
folder.max_children AS folder_max_children,
folder.index_content_id AS folder_index_content_id,
folder.polymorphic_loading AS folder_polymorphic_loading,
folder.rss AS folder_rss,
folder.default_order AS folder_default_order,
parents.level + % (level_1)s AS anon_3
FROM
content
JOIN
folder
ON folder.content_id = content.id
JOIN
parents
ON parents.id = content.container_id
WHERE
content.exclude_nav = false
AND content.container_id IS NOT NULL
AND parents.level <= % (level_2)s
)
SELECT
row_number() OVER (PARTITION BY content.container_id
ORDER BY
content.weight DESC) AS anon_1,
(
SELECT
count(*) AS count_1
FROM
content AS _count_children
WHERE
_count_children.container_id = content.id
)
AS count_children,
content.id AS content_id,
content.added AS content_added,
content.updated AS content_updated,
content.effective AS content_effective,
content.expiration AS content_expiration,
content.exclude_nav AS content_exclude_nav,
content.inherits_parent_acl AS content_inherits_parent_acl,
content.weight AS content_weight,
content.is_fts AS content_is_fts,
content.props AS content_props,
content.content_type_id AS content_content_type_id,
content.owner_id AS content_owner_id,
content.state_id AS content_state_id,
content.container_id AS content_container_id,
folder.content_id AS folder_content_id,
folder.max_children AS folder_max_children,
folder.index_content_id AS folder_index_content_id,
folder.polymorphic_loading AS folder_polymorphic_loading,
folder.rss AS folder_rss,
folder.default_order AS folder_default_order,
parents.level AS level,
account_1.id AS account_1_id,
account_1.login AS account_1_login,
account_1.password AS account_1_password,
account_1.first_name AS account_1_first_name,
account_1.last_name AS account_1_last_name,
account_1.email AS account_1_email,
account_1.created AS account_1_created,
account_1.enabled AS account_1_enabled,
account_1.lost_token AS account_1_lost_token,
state_1.id AS state_1_id,
state_1.name AS state_1_name,
state_1.description AS state_1_description,
content_type_1.id AS content_type_1_id,
content_type_1.name AS content_type_1_name,
content_type_1.icons AS content_type_1_icons,
content_type_1.description AS content_type_1_description,
anon_2.amnesia_multilingual_content_translation_language_id AS anon_2_amnesia_multilingual_content_translation_language__1,
anon_2.amnesia_multilingual_content_translation_content_id AS anon_2_amnesia_multilingual_content_translation_content_i_2,
anon_2.amnesia_multilingual_content_translation_title AS anon_2_amnesia_multilingual_content_translation_title,
anon_2.amnesia_multilingual_content_translation_description AS anon_2_amnesia_multilingual_content_translation_descripti_3,
anon_2.amnesia_multilingual_content_translation_fts AS anon_2_amnesia_multilingual_content_translation_fts,
anon_2._sa_polymorphic_on AS anon_2__sa_polymorphic_on
FROM
content
JOIN
folder
ON folder.content_id = content.id
JOIN
parents
ON parents.id = content.id
JOIN
account AS account_1
ON account_1.id = content.owner_id
JOIN
state AS state_1
ON state_1.id = content.state_id
JOIN
content_type AS content_type_1
ON content_type_1.id = content.content_type_id
JOIN
(
SELECT
amnesia_multilingual.content_translation.language_id AS amnesia_multilingual_content_translation_language_id,
amnesia_multilingual.content_translation.content_id AS amnesia_multilingual_content_translation_content_id,
amnesia_multilingual.content_translation.title AS amnesia_multilingual_content_translation_title,
amnesia_multilingual.content_translation.description AS amnesia_multilingual_content_translation_description,
amnesia_multilingual.content_translation.fts AS amnesia_multilingual_content_translation_fts,
(
SELECT
content.content_type_id
FROM
content
WHERE
amnesia_multilingual.content_translation.content_id = content.id
)
AS _sa_polymorphic_on,
row_number() OVER (PARTITION BY amnesia_multilingual.content_translation.content_id
ORDER BY
amnesia_multilingual.content_translation.language_id = % (param_2)s DESC, amnesia_multilingual.content_translation.language_id = % (param_3)s DESC) AS index
FROM
amnesia_multilingual.content_translation
WHERE
amnesia_multilingual.content_translation.language_id IN
(
% (param_2)s,
% (param_3)s
)
)
AS anon_2
ON anon_2.amnesia_multilingual_content_translation_content_id = content.id
AND anon_2.index = % (index_1)s
ORDER BY
parents.level,
parents.container_id,
parents.weight DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment