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.