Re: building trees from recursive common table expressions

[Dridi Boukelmoune]

Hi,

You probably need a result handler for that.

There's an example here:
http://code.google.com/p/mybatis/wiki/ResultHandlerExample

Dridi

On Fri, Oct 26, 2012 at 7:01 AM, Daniel Lyons <fus...@storytotell.org> wrote:
> Hi,
>
> I've searched for this either wrong or just been confused by what I found so
> I thought I'd ask on the list. I'm using PostgreSQL 9.2. Suppose I have a
> table that looks like this:
>
> CREATE TABLE folders (
> id SERIAL PRIMARY KEY,
> name VARCHAR NOT NULL,
> parent_id INTEGER REFERENCES folders(id),
> UNIQUE (name, parent_id)
> );
>
> This might match a Java object that looks something like this:
>
> public class Folder {
> private String name;
> private List<Folder> children;
> // getters, setters, etc.
> }
>
> I can fetch all the entries below a particular path with a query utilizing a
> common table expression such as this:
>
> WITH RECURSIVE
> folders_rec AS (
> SELECT
> id, name, parent_id,
> '/' || name AS path
> FROM folders
> WHERE name = 'var'
>
> UNION
>
> SELECT
> f.id, f.name, f.parent_id,
> fr.path || '/' || f.name
> FROM folders f
> JOIN folders_rec fr ON fr.id = f.parent_id)
>
> SELECT *
> FROM folders_rec
> ORDER BY parent_id;
>
> The query works and is very handy for eliminating the N+1 problem, which is
> a significant problem in the actual domain I'm in—it's prohibitively
> expensive to perform another fetch at each node. But I don't see how to map
> it with myBatis in a way that preserves the hierarchical structure. What
> should I be searching for or doing differently? Is this covered in the docs
> or a tutorial somewhere and I missed it?
>
> Thanks!



--
Dridi Boukelmoune
Développeur/Formateur

GSM : +33 (0)6 17 91 14 23