I'm working on something using prefetch, and though I thought
I was using it correctly, the SQL that it's generating is
dying with a syntax error, and since I can't even see the
error in the SQL, I figured I'd ask here, perhaps so that the
mere act of typing it will reveal what I'm doing wrong.
I have a table _lessons_ that had previously had columns
"group" and "performer" in it, but I normalized the database
by putting these columns into separate tables. So now part
of my _lesson_ schema looks like this:
__PACKAGE__->belongs_to( group => 'MyDB::Schema::Main::LessonGroup', 'group_id'
);
__PACKAGE__->belongs_to( performer => 'MyDB::Schema::Main::Performer',
'performer_id' );
__PACKAGE__->resultset_attributes({ order_by => 'me.title', prefetch =>
[qw/performer group/ ]} );
When I run a query on this table from a Catalyst app like so:
my $lesson = $c->model->find($id);
I get the following generated SQL according to DBIC_TRACE
(reformatted and with the id value filled in so I can paste it
into the MySQL commandline):
SELECT me.id, me.title, me.performer_id, me.number, me.capo, me.tuning,
me.tab_id, me.audio_id, me.group_id, performer.id, performer.name, group.id,
group.name
FROM lesson me
JOIN performer performer ON ( performer.id = me.performer_id )
JOIN lesson_group group ON ( group.id = me.group_id )
WHERE ( ( me.id = 35 ) )
ORDER BY me.title;
This gives me the error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ON (
group.id = me.group_id )
WHERE ( ( me.id = 35 ) )
ORDER BY me.title' at line 4
Since I can't even see the error in the SQL, let alone in the
DBIC stuff, I must assume that I'm doing something
horrendously wrong. Can someone please tell me what it is?
BTW dropping the prefetch from the attributes, so I just have
__PACKAGE__->resultset_attributes({ order_by => 'me.title'});
works fine, except that I have two extra queries.
Thanks.
Jesse Sheidlower
_______________________________________________
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]
