[Dbix-class] Prefetching resulset subqueries

[thilo.fester@googlemail.com]

Hello everbody,

is it possible to use a resultset as subquery in the FROM clause and
prefetch its results in a way, that it is available as an accessor in the
final resultset?

In detail I would like to do a query like that:

select me.*, properties.me_id, properties.value
from table_me me join ( select id, property from complex_subselect where
condition ) properties
on me.id = properties.id

... and the "properties" should be collapsed to the me-Table.

I would like to use DBIx::Class::ResultClass::HashRefInflator, but I think
this is based on defining a "prefetch" and again this needs to find
accessors in the underlying table_me resultset definition.

At the beginning working around this problem seemed kind of trivial to me,
but now I'm struggling. I thought, I could simply use a sub query in the
from-attribute when searching the resultset and adding "+columns" and
setting "collapse" to a true value. But this way, I am ending up with only
a single property column.

What I want my final result hash to look like is...

[
    {
        me.id => 1,
        me.hello => "world",
        me.some => "thing",
        # ... more fields

        properties => [
            { me_id => 1, value => "something, that I don't get by simply
joining tables" },
            { me_id => 1, value => "the next item in a really long lis" },
            # ... more properties
        ]
    },
    {
        # .... next result object
    }
]

Is it possible to do that in a single database request (with
DBIx::Class::ResultClass::HashRefInflator)?

Thanks in advance : )
Thilo
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20150211/250a862a/attachment.htm>