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

[edwrb .]

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 resu=
lt
  source file:

package MyApp::Schema::ContentView;

use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('content');

my $source =3D __PACKAGE__->result_source_instance();
my $new_source =3D $source->new( $source );
$new_source->source_name( 'MainRecordQuery' );

my $sql =3D<<SQL;
SELECT me.content_id
     , '' as 'main'
     , '' as 'thumbnail'
  FROM  content me
  JOIN  category category ON ( category.category_id =3D me.category_id )
 WHERE  category.category_name =3D ?
   AND  me.active_flg          =3D ? =

 UNION
SELECT  i.content_id
     ,  i.image_name
     ,  i2.image_name
  FROM  image i
 RIGHT  JOIN  content c  ON (c.content_id =3D i.content_id)
        JOIN  image   i2 ON (i.content_id =3D i2.content_id)
 WHERE  i.image_type  =3D ?
   AND  i2.image_type =3D ? =

SQL

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

MyApp::Schema->register_source( 'MainRecordQuery' =3D> $new_source );

1;
----------------------------------------------------------------------

And here is how DBIx::Class is formatting the query:

SELECT * FROM (SELECT me.content_id
     , '' as 'main'
     , '' as 'thumbnail'
  FROM  content me
  JOIN  category category ON ( category.category_id =3D me.category_id )
 WHERE  category.category_name =3D ?
   AND  me.active_flg          =3D ? =

 UNION
SELECT  i.content_id
     ,  i.image_name
     ,  i2.image_name
  FROM  image i
 RIGHT  JOIN  content c  ON (c.content_id =3D i.content_id)
        JOIN  image   i2 ON (i.content_id =3D i2.content_id)
 WHERE  i.image_type  =3D ?
   AND  i2.image_type =3D ? =

) me

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

Thanks in advance!

-Edward

_________________________________________________________________
Climb to the top of the charts!=A0Play the word scramble challenge with sta=
r power.
http://club.live.com/star_shuffle.aspx?icid=3Dstarshuffle_wlmailtextlink_jan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080209/118=
ae8fb/attachment.htm