Hi Jeremy, Thank you so much!!!
However, Unfortunately given example produces an error and says: > ERROR: column pattern.base_folder_id does not exist LINE 1: ...n" INNER JOIN "full_paths" AS "y" ON (("y"."id" = "pattern".... I think In your query, :pattern matched with :y instead of :x with :y when inner join created. I tried to manually fix it but unable to do so. Thanks, Gencer. On Thursday, October 11, 2018 at 10:34:12 PM UTC+3, Jeremy Evans wrote: > > 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 sequel-talk+unsubscr...@googlegroups.com. To post to this group, send email to sequel-talk@googlegroups.com. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.