On Thursday, June 26, 2014 8:17:57 PM UTC-7, Hiroyuki Sato wrote:
>
> Hello Jeremy
>
> Thank you for your advice.
>
> I'll think to store the path as a column.
> It seems faster then rcte_tree for search path.
>
> But I'm wondering move operation slower than rcte_tree
>
Correct, as this would require changing the path for all files/directories
under that path. You can probably do this in a single query though using
substr with string concatenation, though, so I'm not sure it would perform
much worse unless there are a large number of rows that need to be updated.
> So I would like to know much about rcte_tree plugin.
>
> Question
> Could you tell me common method to find specified node like ("/a/b/c")
> with rcte_tree?
> Please tell me if you know alternative approach.
>
There's not a common method. A simplistic approach with multiple queries:
Node.first(:name=>'a', :parent_id=>nil).
children_dataset.first(:name=>'b').
children_dataset.first(:name=>'c')
An approach using joins:
Node.join(:nodes___n1, :parent_id=>:id).
join(:nodes___n2, :parent_id=>:id).
first(:nodes__name=>'a', :n1__name=>'b', :n2__name=>'c')
I'm guessing the join approach is faster, but I don't know enough about
SQLite's query optimizer to say for sure, you'd have to test.
I implemented two method. (find_node_recursive and find_node_ancestors)
>
> Is it possible to use ancestors method for that?
> (Is it return sorted node list?)
>
The CTE query itself will return rows in that order, since that is the
order it calculates the rows. I think most databases will return the rows
in the same order, but I'm not sure about that, you'd have to test.
>
> Thanks
> --
> Hiroyuki Sato.
>
>
> P.S.
> FYI
> rcte_tree plugin does not work OSX marveriks until install sqlite3 >=
> 3.8.3 by myself.
> bundled version is 3.7.13 and it does not support supports common table
> expressions.
>
Yes, I'm aware. Sequel only turns on CTE support on SQLite if version is
>= 3.8.3.
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 http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
