On Thursday, October 11, 2018 at 11:31:06 AM UTC-7, genc wrote:
>
> Hi,
>
> I'm sorry my previous email was mistakenly wrong. This is the correct one.
> I mixed up the queries.
>
>
> with recursive pattern(pattern) as (
> select string_to_array('folder_2/SUB iso', '/') -- input
> ),
> full_paths as (
> select id, base_folder_id, subject, 1 as idx
> from folders
> cross join pattern
> where user_id = 24 and subject ILIKE pattern[1]
> union all
> select x.id, x.base_folder_id, x.subject, idx+ 1
> from folders as x
> cross join pattern
> inner join full_paths as y
> on x.base_folder_id = y.id
> and x.subject ILIKE pattern[idx+ 1]
> )
> select id, subject
> from full_paths
> cross join pattern
> where idx = cardinality(pattern)
>
>
> I couldn't transform this to Sequel properly. Is there any way to do this
> or should do a function?
>
You can do this with Sequel:
DB[:full_paths].
select(:id, :subject).
cross_join(:pattern).
where{{:idx=>cardinality(:pattern)}}.
with(:pattern, DB.select{string_to_array("folder_2/SUB iso", '/')},
:args=>[:pattern]).
with_recursive(:full_paths,
DB[:folders].
select(:id, :base_folder_id, :subject, Sequel[1].as(:idx)).
cross_join(:pattern).
where(:user_id=>24).
where{subject.ilike(pattern.sql_subscript(1))},
DB[Sequel[:folders].as(:x)].
select(:id, :base_folder_id, :subject, Sequel[:idx] + 1).
cross_join(:pattern).
join(Sequel[:full_paths].as(:y),
:id=>:base_folder_id){Sequel.expr{x[:subject].ilike(pattern.sql_subscript(idx+1))}}
)
WITH RECURSIVE "pattern"("pattern") AS (
SELECT string_to_array('folder_2/SUB iso', '/')
),
"full_paths" AS (
SELECT "id", "base_folder_id", "subject", 1 AS "idx"
FROM "folders"
CROSS JOIN "pattern"
WHERE (("user_id" = 24) AND ("subject" ILIKE "pattern"[1] ESCAPE '\'))
UNION ALL (
SELECT "id", "base_folder_id", "subject", ("idx" + 1)
FROM "folders" AS "x"
CROSS JOIN "pattern"
INNER JOIN "full_paths" AS "y"
ON (("y"."id" = "pattern"."base_folder_id")
AND ("x"."subject" ILIKE "pattern"[("idx" + 1)] ESCAPE '\')))
)
SELECT "id", "subject"
FROM "full_paths"
CROSS JOIN "pattern"
WHERE ("idx" = cardinality("pattern"))
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
