[Dbix-class] use array and composite type to prefetch in Pg

Sam Wong sam at hellosam.net
Wed Mar 18 16:54:02 GMT 2009


I just realized my post was corrupted...

Hi there,

I am new to ORM and to DBIx::Class-

In PostgreSQL fetching of composite types and array is supported, as documented in
http://www.postgresql.org/docs/8.3/interactive/rowtypes.html
http://www.postgresql.org/docs/8.3/interactive/arrays.html

prefetch query can be constructed like this...

SELECT parents.*, 
    array(SELECT childs1.* WHERE childs1.parent_id = parents.id)::childs1,
    array(SELECT childs2.* WHERE childs2.parent_id = parents.id)::childs2,
    ...

which will returns something like this...
id | childs1                                       | child2
1  | ({child_id: x}, {child_id: y}, {child_id: z}) | ...
2  | ({child_id: q}, {child_id: s})                | ...
3  | ()
4  | ({child_id: 1}, {child_id: 2})

This will return only ONE row for EACH parents row, no matter how many
children's were out there.
If the parent has multiple child relations, prefetching multiple child
relations will also return only ONE row per parent, which is a huge
performance benefits.

In DBD::Pg, Array will be "inflated" into array ref automatically,
composite type was not inflated to hash yet by DBD::Pg.

Does anyone think of this? Any chance that this could be implemented easily?

Thanks,
Sam




More information about the DBIx-Class mailing list