Interesting read...apologies if it has been posted already. http://www.oracle.com/technology/pub/articles/saternos-rails.html -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060706/619e650a/attachment.html
> Interesting read...apologies if it has been posted already.Yes Oracle staff seem to ignore some of the jewels of Ror like act_as_tree : Quote ------------------------------------------------------------------------------- The query to construct the corporate hierarchy is as follows: SELECT tree.*, LEAD (hierarchy_level) OVER (ORDER BY seq) next_level FROM ( SELECT CONNECT_BY_ROOT last_name top_node_name, (last_name || '', '' || first_name ||'' (''||job_title||'')'') employee_name, emp.id employee_id, SYS_CONNECT_BY_PATH (last_name, ''->'') node_path, LEVEL hierarchy_level, ROWNUM seq FROM( SELECT e.*, j.job_title FROM employees e, jobs j WHERE e.job_id = j.id ) emp START WITH emp.job_title= ''President'' CONNECT BY PRIOR emp.id = manager_id ORDER SIBLINGS BY emp.id ) tree ORDER BY seq --------------------------------------------------------
I expect nothing less from Oracle... lots of proprietary SQL, poor documentation, and lots of product plugs. :) On 7/6/06, Mathieu Chappuis <mathieu.chappuis.lists@gmail.com> wrote:> > > Interesting read...apologies if it has been posted already. > > Yes > > Oracle staff seem to ignore some of the jewels of Ror like act_as_tree : > > Quote > > ------------------------------------------------------------------------------- > The query to construct the corporate hierarchy is as follows: > > SELECT tree.*, > LEAD (hierarchy_level) OVER (ORDER BY seq) next_level > FROM ( > SELECT CONNECT_BY_ROOT last_name top_node_name, > (last_name || '', '' || first_name ||'' > (''||job_title||'')'') employee_name, > emp.id employee_id, > SYS_CONNECT_BY_PATH (last_name, ''->'') node_path, > LEVEL hierarchy_level, > ROWNUM seq > FROM( > SELECT e.*, j.job_title > FROM employees e, jobs j > WHERE e.job_id = j.id > ) emp > START WITH emp.job_title= ''President'' > CONNECT BY PRIOR emp.id = manager_id > ORDER SIBLINGS BY emp.id > ) tree > ORDER BY seq > -------------------------------------------------------- > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060706/a0ad3262/attachment.html
On 7/6/06, Mathieu Chappuis <mathieu.chappuis.lists@gmail.com> wrote:> Oracle staff seem to ignore some of the jewels of Ror like act_as_tree : > > Quote > ------------------------------------------------------------------------------- > The query to construct the corporate hierarchy is as follows: > > SELECT tree.*, > LEAD (hierarchy_level) OVER (ORDER BY seq) next_level > FROM ( > SELECT CONNECT_BY_ROOT last_name top_node_name, > (last_name || '', '' || first_name ||'' (''||job_title||'')'') employee_name, > emp.id employee_id, > SYS_CONNECT_BY_PATH (last_name, ''->'') node_path, > LEVEL hierarchy_level, > ROWNUM seq > FROM( > SELECT e.*, j.job_title > FROM employees e, jobs j > WHERE e.job_id = j.id > ) emp > START WITH emp.job_title= ''President'' > CONNECT BY PRIOR emp.id = manager_id > ORDER SIBLINGS BY emp.id > ) tree > ORDER BY seqOracle has special syntax (CONNECT BY) for selecting tree-like data efficiently a single query, something I don''t think acts_as_tree doesn''t make use of (and I''m too lazy to check). An oracle only version of acts_as_tree could be made much more efficient than the generic database version. Tom
Tom Ward wrote:> Oracle has special syntax (CONNECT BY) for selecting tree-like data > efficiently a single query, something I don''t think acts_as_tree > doesn''t make use of (and I''m too lazy to check). An oracle only > version of acts_as_tree could be made much more efficient than the > generic database version.Yep. In some cases the proprietary approach actually does make sense. Might as well get some value out of all that $$$.