(self, op_filters)
return stmt
Postgres child CTE
Postgres child CTE
Postgres child CTE
def get_child_cte(self, op_filters):
        '''Postgres child CTE'''
        where = self._get_cte_where(op_filters)

        stmt = '''WITH RECURSIVE res(parent_keyword_code, parent_key, child_keyword_code, child_key, alias, path,  depth) AS (
                  SELECT
                  r."parent_keyword_code", p1."name",
                  r."child_keyword_code", p2."name",
                        p1."alias",
                        CAST(ARRAY[r."parent_keyword_code"] AS TEXT),
                  1
                 FROM "keyword_map" AS r, "base_keyword" AS p1, "base_keyword" AS p2
                 WHERE (%s)
                 AND p1."code" = r."parent_keyword_code" AND p2."code" = r."child_keyword_code"
                 UNION ALL
                 SELECT
                  r."parent_keyword_code", p1."name",
                  r."child_keyword_code", p2."name",
                        p2."alias",
                        path || r."parent_keyword_code",
                  ng.depth + 1
                 FROM "keyword_map" AS r, "base_keyword" AS p1, "base_keyword" AS p2,
                  res AS ng
                 WHERE r."parent_keyword_code" = ng."child_keyword_code" and depth < 4
                 AND p1."code" = r."parent_keyword_code" AND p2."code" = r."child_keyword_code"
                )

        Select * from res;'''%where

        return stmt
def get_child_cte ( self , op_filters ) : where = self . _get_cte_where ( op_filters ) stmt = '''WITH RECURSIVE res(parent_keyword_code, parent_key, child_keyword_code, child_key, alias, path,  depth) AS (
                  SELECT
                  r."parent_keyword_code", p1."name",
                  r."child_keyword_code", p2."name",
                        p1."alias",
                        CAST(ARRAY[r."parent_keyword_code"] AS TEXT),
                  1
                 FROM "keyword_map" AS r, "base_keyword" AS p1, "base_keyword" AS p2
                 WHERE (%s)
                 AND p1."code" = r."parent_keyword_code" AND p2."code" = r."child_keyword_code"
                 UNION ALL
                 SELECT
                  r."parent_keyword_code", p1."name",
                  r."child_keyword_code", p2."name",
                        p2."alias",
                        path || r."parent_keyword_code",
                  ng.depth + 1
                 FROM "keyword_map" AS r, "base_keyword" AS p1, "base_keyword" AS p2,
                  res AS ng
                 WHERE r."parent_keyword_code" = ng."child_keyword_code" and depth < 4
                 AND p1."code" = r."parent_keyword_code" AND p2."code" = r."child_keyword_code"
                )

        Select * from res;''' % where return stmt