I'm afraid I can't provide any useful comment on your DBIx::Class specific questions. However if arbitrary SQL is as important to you as the ORM part I would like to suggest perhaps a cursory look at SQL::DB. (Disclaimer: I am the author of that module).

The following is a real example from one of my own projects:

    my @nodes = $self->fetch(
        select    => [
            $nodes->id,
            $nodes->path,
            coalesce(
                $nodest->nav,
                $nodest->title,
                $nodes->nav,
                $nodes->title)->as('nav'),
        ],
        from      => [$nodes,$nodes2],
        left_join => $nodest,
        on        => ($nodest->transof == $nodes->id) &
                     ($nodest->lang == $self->lang),
        where     => $nodes2->lft->between($nodes->lft, $nodes->rht) &
                     ($nodes2->id == $id),
        order_by  => $nodes->lft,
    );
[download]

The objects returned from this query are blessed into a dynamically created class that contains only 'id', 'path' and 'nav' methods. The advantage of this style of query is that you can write anything you like, and retrieve exactly (and only) the information you want from the database. The disadvantage (compared with ORM-style DBIx::Class) is that you have to construct for each query with exactly what you want. Ie: no automatic joining of tables, and no auto-inflation.

Unfortunately I have to follow with the caveat that SQL::DB is still under development and suffers from lack of documentation and exposure outside my own projects. However (and here the true motivation for this comment) I would certainly appreciate feedback and knowing how SQL::DB is/isn't suitable for your task.