Hello!
OVERVIEW
Imagine that we have some data in DB, which can be represented in nested
structure Eg. categories tree:
CREATE TABLE categories (
id INT NOT NULL,
parent_id INT NULL ,
title TEXT,
CONSTRAINT "subcategories" FOREIGN KEY("parent_id") REFERENCES
"categories"("id")
);
PROBLEM
Now we want to retrieve all subcategories for category(id = $id) with 3rd
nested level. For example we will want retrieve the next structure:
Category(id=$id)
- Category 1(id=id1, parent_id=$id)
- - Category 1-1(id=id3, parent_id=id1)
- - Category 1-2(id=id4, parent_id=id1)
- - Category 1-3(id=id5, parent_id=id1)
- Category 2(id=id2, parent_id=$id)
- - Category 2-1(id=id6, parent_id=id2)
In our code we can do this vie DBIx::Class
$rs = $schema
->resultset('Categories')
->search(
{
'me.id' =>$id
},
{
prefetch => {'subcategories' => 'subcategories'}
}
);
And now we can call something like
$rs->first->subcategories->first->subcategories->first->...->title
And we will get query like ... LEFT JOIN ... LEFT JOIN ...
If we want the 4th nested level, we will get (LEFT JOIN) x 3.
If we want the Mth nested level, we well get (LEFT JOIN) x M.
SOLVE
So, to solve this multiple LEFT JOIN we've created some stored procedure in
DB, which returns the same data as multiple LEFT JOIN
(category_descendants(id)). but returns it very fast.
QUESTION
How can I convert raw data, which could be returned by calling
category_descendants($id), to a "good" DBIx::Class object?
I want to call category_descendants($id), and then I want to use something
like this:
$rs->first->subcategories->first->subcategories->first->...->title
Thank you!
--
//wbr, Dmitry L.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
