[Dbix-class] relationships and custom search

[Matt S Trout]

On Wed, May 06, 2009 at 05:47:21AM -0700, Morgon Hed wrote:
> 
> Hi,
> 
> can someone please explain to me if it is possible to associate a custom search to a ResultSource?
> 
> My problem is the following: I have 2 tables with a one-to-many relationships.
> Both tables have a XMLType-column that I can get to only when I do a search like the following:
> 
> my $rs = $schema->resultset('Stream')->search(
>                                                {},
>                                                    {
>                                                      select => [ 'id', 'basename', { 'xmltype.getCLobVal' => 'xml' } ],
>                                                      as     => [qw/id basename xml/],
>                                                    }
>                                              );
> 
> This generates SQL that does not simply query for the column "xml", but inserts a "xmltype.getclobval(xml)".
> 
> The problem now is that when I try to do retrieve the associated rows from the child-table DBIx::Class generates a "standard" query (i.e. just listing all columns) which does not work for XMLType-columns.
> 
> So what I would need is a way to tell DBIx::Class not to generate "SELECT me.id, me.col1, me.col2, me.xml FROM table me WHERE ( me.id = ? )" but 
> ""SELECT me.id, me.col1, me.col2, xmltype.getclobvalue(me.xml) FROM table me WHERE ( me.id = ? )" whenever I navigate the relationships.

Don't. Eliminate the xml col from the default set to retrieve and use the
+columns / resultset method approach. Please get that part working first,
then we can discuss how to make this work across relationship calls.

-- 
        Matt S Trout         Catalyst and DBIx::Class consultancy with a clue
     Technical Director      and a commit bit: http://shadowcat.co.uk/catalyst/
 Shadowcat Systems Limited
  mst (@) shadowcat.co.uk        http://shadowcat.co.uk/blog/matt-s-trout/