2024-09-19 honeysql | Clojure Slack Archive

honeysql

jabhi207 2024-09-19T09:11:47.546879Z

How do I add CYCLE id SET is_cycle USING cycle_path in the query to avoid cycle in recursive query

{:with-recursive
   [[:employee-rl
     {:union-all
      [{:select [:id [(utils-sql-casts/as-jsonb-array [:array []]) :reporting-line]]
        :from [:employees]
        :where [:= :manager 123]}
			 {:select [:rem.id
								 [[:array_prepend [:jsonb_build_object
																	 "employee-id", :rem.manager]
									 :er.reporting-line]]]
				:from [[:employees :rem]]
				:inner-join [[:employee-rl :er] [:= :rem.manager :er.id]]}]}]]
	 :select [:ejr.employee-id
						[[:array_to_json :erl.reporting-line] :reporting-line]]
	 :from [[:employees :e]]
	 :inner-join [[:employee-rl :erl] [:= :e.id :erl.id]]}

I tried adding it using raw

(honey.sql/format
 {:with-recursive
	[[:employee-rl
		{:union-all
		 [{:select [:id [(utils-sql-casts/as-jsonb-array [:array []]) :reporting-line]]
			 :from [:employees]
			 :where [:= :manager 123]}
			{:select [:rem.id
								[[:array_prepend [:jsonb_build_object
																	"employee-id", :rem.manager]
									:er.reporting-line]]]
			 :from [[:employees :rem]]
			 :inner-join [[:employee-rl :er] [:= :rem.manager :er.id]]}]}]]
	:raw ["CYCLE id SET is_cycle USING cycle_path"]
	:select [:ejr.employee-id
					 [[:array_to_json :erl.reporting-line] :reporting-line]]
	:from [[:employees :e]]
	:inner-join [[:employee-rl :erl] [:= :e.id :erl.id]]}
 {:inline true})

But this generates unexpected output

CYCLE id SET is_cycle USING cycle_path WITH RECURSIVE employee_rl AS (
    SELECT
        id,
        CAST(ARRAY[] AS jsonb[]) AS reporting_line FROM employees
    WHERE
        manager = 123
    UNION ALL
    SELECT
        rem.id,
        ARRAY_PREPEND(JSONB_BUILD_OBJECT('employee-id', rem.manager), er.reporting_line)
    FROM
        employees AS rem
        INNER JOIN employee_rl AS er ON rem.manager = er.id
)
SELECT
    ejr.employee_id,
    ARRAY_TO_JSON(erl.reporting_line) AS reporting_line
FROM
    employees AS e
    INNER JOIN employee_rl AS erl ON e.id = erl.id

Where should i place the raw query to generate the output

WITH RECURSIVE employee_rl AS (
    SELECT
        id,
        CAST(ARRAY[] AS jsonb[]) AS reporting_line
    FROM
        employees
    WHERE
        manager = 123
    UNION ALL
    SELECT
        rem.id,
        ARRAY_PREPEND(JSONB_BUILD_OBJECT('employee-id', rem.manager), er.reporting_line)
    FROM
        employees AS rem
        INNER JOIN employee_rl AS er ON rem.manager = er.id) CYCLE id SET is_cycle USING cycle_path
    SELECT
        ejr.employee_id, ARRAY_TO_JSON(erl.reporting_line) AS reporting_line FROM employees AS e
    INNER JOIN employee_rl AS erl ON e.id = erl.id

jabhi207 2024-09-19T09:12:35.427519Z

I am using postgresql version 14 so CYCLE is supported

p-himik 2024-09-19T09:46:21.325389Z

Given the current impl of :with and :with-recursive, it doesn't seem like you can do that without creating your own clause. E.g. you can override :with with a formatter that accepts not two items but three, with the last optional one dictating CYCLE.

p-himik 2024-09-19T09:47:36.090929Z

But that's a decent amount of work, especially given that format-with is private. So it seems to be that the best approach would be to extend HoneySQL itself so that :with accepts three parts.

p-himik 2024-09-19T09:51:47.104569Z

And, of course, the third part should not be hard-coded to support only CYCLE. PostgreSQL supports also SEARCH and maybe other DBMS's support other clauses.

p-himik 2024-09-19T10:52:22.850959Z

@seancorfield Checked all the other databases that I know of - only PostgreSQL seems to support extra settings in CTEs, and only for WITH RECURSIVE. It's not hard to extend honey.sql/format-with so it has [x expr extra :as with] instead of [x expr :as with]. But I don't know how best to process that extra. If CYCLE and SEARCH (the only two things supported by PostgreSQL in that context) are used with function-like syntax it will break any code that already uses :cycle or :search as functions. If they're made into proper SQL clauses akin to :select, it might also be confusing and potentially conflicting with other DBs. Maybe it makes sense to register functions like :pg/cycle and :pg/search?

seancorfield 2024-09-19T16:20:48.046769Z

One of you please create a GH issue with links to the relevant PG docs (since I don't use PG), and I'll take a look...

p-himik 2024-09-19T17:10:10.627019Z

Done: https://github.com/seancorfield/honeysql/issues/542

👍🏻 1

jabhi207 2024-09-20T04:44:33.765529Z

@p-himik Thank you for your effort and time

seancorfield 2024-10-12T19:57:45.820639Z

@jabhi207 The solution I've implemented for issue 542 would support {:with-recursive [[:employee-rl {:union-all ...} :cycle :id :set :is-cycle :using :cycle-path]]}

2024-09-19T17:10:06.745439Z

2024-10-13T19:17:09.113159Z

Clojurians Log v2

honeysql