Thanks Matt. That 'as' trick took care of the SQL parsing issue.
On a related note, is there a best practice for iterating through the
result-set from these
custom resource files by chance? I thought something like the following would
work:
my $rs = [ $schema->resultset( 'MainRecordQuery' )->search( {},
{
bind => [ $category, 'Y', 'thumbnail', 'main']
}
) ];
while (my $data = $rs->next) {
print $data->thumbnail . "\n";
}
but it fails with the following error:
'Can't call method "next" on unblessed reference' (from the while loop)
A closer look at the data structure coming back from the resultsource looks
like it's an array of hashes?
Cheers,
-ed
> Date: Sun, 10 Feb 2008 10:23:07 +0000
> From: [EMAIL PROTECTED]
> To: [email protected]
> Subject: Re: [Dbix-class] Complex query in custom resultsource file.
>
> 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/
>
> _______________________________________________
> 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]
_________________________________________________________________
Connect and share in new ways with Windows Live.
http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008_______________________________________________
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]
