I've fixed it by manually giving field names. However it returns empty.
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 ] )) 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 ((
"y"."id" = "y"."base_folder_id"
)
AND ( "x"."subject" ILIKE "pattern" [ ( "idx" + 1 ) ] )))) SELECT
"id",
"subject"
FROM
"full_paths"
CROSS JOIN "pattern"
WHERE
(
"idx" = CARDINALITY ( "pattern" ))
On Thursday, October 11, 2018 at 10:49:55 PM UTC+3, genc wrote:
>
> 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.
