foo.sql

WITH RECURSIVE props_parents(id, props, container_id, level) AS 
(
   SELECT
      content_1.id AS id,
      content_1.props AS props,
      content_1.container_id AS container_id,
      % (param_1)s AS level 
   FROM
      content AS content_1 
   WHERE
      content_1.id = content.id 
   UNION ALL
   SELECT
      content.id AS id,
      content.props AS props,
      content.container_id AS container_id,
      props_parents.level + % (level_1)s AS anon_2 
   FROM
      content 
      JOIN
         props_parents 
         ON props_parents.container_id = content.id
)
SELECT
   content.id AS content_id,
   content.added AS content_added,
   content.updated AS content_updated,
   content.title AS content_title,
   content.description AS content_description,
   content.effective AS content_effective,
   content.expiration AS content_expiration,
   content.exclude_nav AS content_exclude_nav,
   content.weight AS content_weight,
   content.content_type_id AS content_content_type_id,
   content.container_id AS content_container_id,
   content.owner_id AS content_owner_id,
   content.state_id AS content_state_id,
   content.fts AS content_fts,
   content.is_fts AS content_is_fts,
   content.props AS content_props,
   content.inherits_parent_acl AS content_inherits_parent_acl,
   (
      SELECT
         json_object_agg(foo.key, foo.value) 
      FROM
         props_parents,
         json_each(props_parents.props) AS foo
   )
   AS anon_1,
   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.email AS account_1_email,
   account_1.enabled AS account_1_enabled,
   account_1.created AS account_1_created,
   account_1.lost_token AS account_1_lost_token,
   account_1.last_name AS account_1_last_name,
   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.description AS content_type_1_description,
   content_type_1.icons AS content_type_1_icons 
FROM
   content 
   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 
WHERE
   content.id = % (pk_1)s