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

[Andreas Pronakis]

I will have to apologise for being 'hasty'.

The SQL is fine (the 'me' is actually there, I was just assuming it was part of the {bin} params because it was appearing on the next line with :$value in DBIC_TRACE), so that's fine.

However, the error remains, where I get an empty arrayref as the result even though the database has values for the SQL.

Further investigation revealed that:
1. I can only get a custom SQL to work only if I do not use the PRIMARY_KEY as part of the WHERE clause.  I haven't fully investigated this, but if I change the WHERE to any other column, I get back a ResultSet.

2. Following on thr last sentence from point 1 - I do not need to call the custom ResultSet within an arrayref i.e.
$story_obj = $schema->resultset('FindStory')->search({}, { bind => [$value]});

is working fine (returns a ResultSet) - notice the missing [...] around the call.  Is this something that was changed resentrly but not updated in the Cookbook docs or something else?



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

> From: Andreas Pronakis <andr3as at yahoo.com>
> Subject: [Dbix-class] Re: Creating custom resultset error and MySQL
> To: dbix-class at lists.scsys.co.uk
> Date: Friday, July 4, 2008, 1:44 PM
> 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
> 
> 
>       
> 
> _______________________________________________
> 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/dbix-class@lists.rawmode.org