okrs

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

node_id

int8

√

null

root_node_id

int8

√

null

parent_node_id

int8

√

null

path

"okr"."ltree"

2147483647

√

null

level

int4

√

null

organization_id

int8

√

null

okr_term_id

int8

√

null

objective_id

int8

√

null

objective_progress_rate_updated_at

timestamptz

35,6

√

null

key_result_ids

_int8

√

null

child_node_ids

_int8

√

null

group_ids

_int8

√

null

group_member_ids

_int8

√

null

slack_integration_enabled

bool

√

null

chatwork_integration_enabled

bool

√

null

teams_integration_enabled

bool

√

null

display_order

int4

√

null

created_at

timestamptz

35,6

√

null

updated_at

timestamptz

35,6

√

null

View Definition

SELECT node.id AS node_id,
    (okr.ltree2text(okr.subpath(node.path, 0, 1)))::bigint AS root_node_id,
        CASE
            WHEN (okr.nlevel(node.path) = 1) THEN NULL::bigint
            ELSE (okr.ltree2text(okr.subpath(node.path, '-2'::integer, 1)))::bigint
        END AS parent_node_id,
    node.path,
    okr.nlevel(node.path) AS level,
    node.organization_id,
    node.okr_term_id,
    node_objective.id AS objective_id,
    node_objective.progress_rate_updated_at AS objective_progress_rate_updated_at,
    node_key_results.key_result_ids,
    child_nodes.child_node_ids,
    node_groups.group_ids,
    node_groups.group_member_ids,
        CASE
            WHEN (slackic.integration_channels_count > 0) THEN true
            ELSE false
        END AS slack_integration_enabled,
    node.chatwork_integration_enabled,
    node.teams_integration_enabled,
    node.display_order,
    GREATEST(node.created_at, node_objective.created_at, node_key_results.created_at) AS created_at,
    GREATEST(node.updated_at, node_objective.updated_at, node_key_results.updated_at) AS updated_at
   FROM (((((okr.okr_nodes node
     LEFT JOIN ( SELECT gon.okr_node_id,
            array_agg(DISTINCT gon.group_id) AS group_ids,
            array_agg(DISTINCT gm.user_id) AS group_member_ids
           FROM ((okr.group_okr_nodes gon
             LEFT JOIN okr.group_members gm ON ((gm.group_id = gon.group_id)))
             LEFT JOIN okr.groups g ON ((g.id = gon.group_id)))
          WHERE ((gm.user_id IS NOT NULL) AND (g.deleted_at IS NULL))
          GROUP BY gon.okr_node_id) node_groups ON ((node_groups.okr_node_id = node.id)))
     LEFT JOIN okr.objectives node_objective ON ((node_objective.okr_node_id = node.id)))
     LEFT JOIN ( SELECT k.okr_node_id,
            array_agg(k.id ORDER BY k.display_order, k.id) AS key_result_ids,
            max(k.created_at) AS created_at,
            max(k.updated_at) AS updated_at
           FROM okr.key_results k
          GROUP BY k.okr_node_id) node_key_results ON ((node_key_results.okr_node_id = node.id)))
     LEFT JOIN ( SELECT okr.subpath(o.path, 0, (okr.nlevel(o.path) - 1)) AS parent_path,
            array_agg(o.id ORDER BY o.display_order, o.id) AS child_node_ids
           FROM okr.okr_nodes o
          GROUP BY (okr.subpath(o.path, 0, (okr.nlevel(o.path) - 1)))) child_nodes ON ((child_nodes.parent_path OPERATOR(okr.=) node.path)))
     LEFT JOIN ( SELECT s.okr_node_id,
            count(s.okr_node_id) AS integration_channels_count
           FROM okr.okr_node_slack_integration_channels s
          GROUP BY s.okr_node_id) slackic ON ((slackic.okr_node_id = node.id)))
  ORDER BY (okr.nlevel(node.path)),
        CASE
            WHEN (okr.nlevel(node.path) = 1) THEN NULL::bigint
            ELSE (okr.ltree2text(okr.subpath(node.path, '-2'::integer, 1)))::bigint
        END, node.display_order, node.id;

Columns

View Definition

Possibly Referenced Tables/Views