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.
