[Dbix-class] Complex query in custom resultsource file.

[Matt S Trout]

On Sat, Feb 09, 2008 at 09:36:03PM +0000, edwrb . wrote:
> 
> Hi there,
> 
>   So I'm trying to implement a complex query in DBIx::Class using a custom
>   ResultSource file, but the query ends up always failing due to some extra
>   stuff that is being automatically tacked on to the query.  Here's my result
>   source file:
> 
> package MyApp::Schema::ContentView;
> 
> use base qw/DBIx::Class/;
> 
> __PACKAGE__->load_components(qw/PK::Auto Core/);
> __PACKAGE__->table('content');
> 
> my $source = __PACKAGE__->result_source_instance();
> my $new_source = $source->new( $source );
> $new_source->source_name( 'MainRecordQuery' );
> 
> my $sql =<<SQL;
> SELECT me.content_id
>      , '' as 'main'
>      , '' as 'thumbnail'
>   FROM  content me
>   JOIN  category category ON ( category.category_id = me.category_id )
>  WHERE  category.category_name = ?
>    AND  me.active_flg          = ? 
>  UNION
> SELECT  i.content_id
>      ,  i.image_name
>      ,  i2.image_name
>   FROM  image i
>  RIGHT  JOIN  content c  ON (c.content_id = i.content_id)
>         JOIN  image   i2 ON (i.content_id = i2.content_id)
>  WHERE  i.image_type  = ?
>    AND  i2.image_type = ? 
> SQL
> 
> $new_source->name( \"(${sql})" );

I think

  $new_source->name( \"(${sql}) AS" );

would do the trick.

> Note the format of that me alias at the end.  Whenever I change that end bit to 'AS me'
> and run it in an sql-shell, it runs great.  So I was wondering if this is how DBIx::Class
> is suppose to be formatting these ResultSource queries.  If so, how do I format the query
> in my ResultSource file to get it to work like it should?

We don't AS in FROM clauses by default because some databases (older Oracles
IIRC) don't support it.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/