[Dbix-class] Re: Creating custom resultset error and MySQL

[Andreas Pronakis]

Since i didn't get any replies to this question I re-read my posting and it probably doesn't make much sense :-( sorry about that.  I'll try to explain it again:

First of all I'm running DBIx::Class version 0.08010

Basically, I've followed the example on the Cookbook regarding 'Arbitrary SQL through a custom ResultSource' (the code is shown further down).  However When I actually try to call the result set I get the error message 

'Can't call method "headline" on an undefined value at App_custom_sql.pl line 25.'  

Which is because the returned ResultSet is empty.

By turning on DBIC_TRACE the SQL that get's generated is:

SELECT me.story_id, me.journalist_user_id, me.revision, me.story_category_id, me.story_status_id, me.distribution_flag, me.date, me.created_timestamp, me.last_edit_timestamp, me.true_timestamp, me.order_id, me.delete_flag, me.publish_revision, me.creator_user_id, me.published_timestamp, me.external_code, me.external_sequence, me.type_id, me.expiry_datetime, me.source_id, me.headline, me.body, me.introduction, me.abstract, me.author_notes, me.by_line FROM (SELECT se.*, sb.* FROM story_edit se INNER JOIN story_body sb ON se.story_id = sb.story_id WHERE se.story_id = ? AND se.delete_flag = 0)
 me: '2134660'


Copying and pasting the SQL directly into MySQL (version 4.1) I get the following SQL error:

ERROR 1248 (42000): Every derived table must have its own alias


If in the manual run of the SQL I add the word 'me' at the very end (after the final closing bracket) everything works, however I do not know how to do that when calling the SQL from the ResultSet I've created in DBIx::Class, I've tried adding the work 'me' at the very end of the \<<SQL string but then I end up with a DBI Exception error.


Can anyone give me a helping hand on this?


Thanks once again

Andreas

PS. All other postings answers have come in very handy - thanks a lot.


The code for the custom ResultSet:


my $source = __PACKAGE__->result_source_instance();
my $new_source = $source->new($source);

$new_source->source_name('FindStory');

$new_source->name(\<<SQL);
(SELECT se.*, sb.* FROM story_edit se INNER JOIN story_body sb ON se.story_id = sb.story_id WHERE se.story_id = ? AND se.delete_flag = 0) self
SQL


DBIC::Digital::Model::DB::Schema->register_source('FindStory' => $new_source);


1;


Then called from a perl script like so:

$story_obj = [ $schema->resultset('FindStory')->search({}, {bind => [2134660]}) ];












--- On Tue, 7/1/08, Andreas Pronakis <andr3as at yahoo.com> wrote:

> From: Andreas Pronakis <andr3as at yahoo.com>
> Subject: Creating custom resultset error and MySQL
> To: dbix-class at lists.scsys.co.uk
> Date: Tuesday, July 1, 2008, 12:58 PM
> I've tried creating a custom resultset, following the
> instructions on DBIx::Class::Cookbook docs, but when
> calling the search() method on it it always returns empty,
> so I enabled DBIC_TRACE and the following sql was being
> executed:
> 
> 
> SELECT me.story_id, me.journalist_user_id, me.revision,
> me.story_category_id, me.story_status_id,
> me.distribution_flag, me.date, me.created_timestamp,
> me.last_edit_timestamp, me.true_timestamp, me.order_id,
> me.delete_flag, me.publish_revision, me.creator_user_id,
> me.published_timestamp, me.external_code,
> me.external_sequence, me.type_id, me.expiry_datetime,
> me.source_id, me.headline, me.body, me.introduction,
> me.abstract, me.author_notes, me.by_line FROM (SELECT
> se.story_id, se.journalist_user_id, se.revision,
> se.story_category_id, se.story_status_id,
> se.distribution_flag, se.date, se.created_timestamp,
> se.last_edit_timestamp, se.true_timestamp, se.order_id,
> se.delete_flag, se.publish_revision, se.creator_user_id,
> se.published_timestamp, se.external_code,
> se.external_sequence, se.type_id, se.expiry_datetime,
> se.source_id, sb.revision, sb.headline, sb.body,
> sb.introduction, sb.abstract, sb.author_notes, sb.by_line
> FROM story_edit AS se INNER JOIN
>  story_body AS sb ON se.story_id = sb.story_id WHERE
> se.story_id = ? AND se.delete_flag = 0)
> me: '2134660'
> 
> By copying and pasting the SQL directly into the DB (MySQL
> 4.1) the following error is being returned - after
> substituting ? for 2134660:
> ERROR 1248 (42000): Every derived table must have its own
> alias
> 
> When I change the SQL on the manual run to include at the
> end '...) AS me;' (excluding the quotes) it returns
> the correct results.
> 
> 
> The actual SQL that $new_source->name(\<<SQL)
> is:
> (SELECT se.story_id, se.journalist_user_id, se.revision,
> se.story_category_id, se.story_status_id,
> se.distribution_flag, se.date, se.created_timestamp,
> se.last_edit_timestamp, se.true_timestamp, se.order_id,
> se.delete_flag, se.publish_revision, se.creator_user_id,
> se.published_timestamp, se.external_code,
> se.external_sequence, se.type_id, se.expiry_datetime,
> se.source_id, sb.revision, sb.headline, sb.body,
> sb.introduction, sb.abstract, sb.author_notes, sb.by_line
> FROM story_edit AS se INNER JOIN story_body AS sb ON
> se.story_id = sb.story_id WHERE se.story_id = ? AND
> se.delete_flag = 0)
> SQL
> 
> 
> 
> Has anyone else come across this error?
> Can anyone confirm if this is a bug with
> DBIx::Class::ResultSet?
> Is there a fix for it?
> 
> 
> I have also tried the following (which is basically the
> same only without the explicit JOIN, with exactly the same
> results.
> 
> (SELECT se.story_id, se.journalist_user_id, se.revision,
> se.story_category_id, se.story_status_id,
> se.distribution_flag, se.date, se.created_timestamp,
> se.last_edit_timestamp, se.true_timestamp, se.order_id,
> se.delete_flag, se.publish_revision, se.creator_user_id,
> se.published_timestamp, se.external_code,
> se.external_sequence, se.type_id, se.expiry_datetime,
> se.source_id, sb.revision, sb.headline, sb.body,
> sb.introduction, sb.abstract, sb.author_notes, sb.by_line
> FROM story_edit AS se, story_body AS sb WHERE se.story_id =
> ? AND se.delete_flag = 0 AND se.story_id = sb.story_id)
> 
> 
> Even the simpler SQL:
> 
> SELECT me.story_id, me.journalist_user_id, me.revision,
> me.story_category_id, me.story_status_id,
> me.distribution_flag, me.date, me.created_timestamp,
> me.last_edit_timestamp, me.true_timestamp, me.order_id,
> me.delete_flag, me.publish_revision, me.creator_user_id,
> me.published_timestamp, me.external_code,
> me.external_sequence, me.type_id, me.expiry_datetime,
> me.source_id, me.headline, me.body, me.introduction,
> me.abstract, me.author_notes, me.by_line FROM (SELECT se.*,
> sb.* FROM story_edit se INNER JOIN story_body sb ON
> se.story_id = sb.story_id WHERE se.story_id = 2134660 AND
> se.delete_flag = 0);
> 
> Deriving from the \<<SQL);
> (SELECT se.*, sb.* FROM story_edit se INNER JOIN story_body
> sb ON se.story_id = sb.story_id WHERE se.story_id = ? AND
> se.delete_flag = 0)
> SQL
> 
> Returns the same MySQL error, adding 'AS me' at the
> end solves the problem
> 
> Any suggestions/recommendations would be greatly
> appreciated.
> 
> Thank you in advance
> Andreas