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})" );
MyApp::Schema->register_source( 'MainRecordQuery' => $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 = 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 = ?
) me
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?
Thanks in advance!
-Edward
_________________________________________________________________
Climb to the top of the charts! Play the word scramble challenge with star
power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
