Anthony Gladdish wrote:
> Hi,
>
> Using DBIC v0.08102_07.
>
> My Result class relationships:
>
> 1. Course.pm
> 2. Event.pm:
> __PACKAGE__->belongs_to( 'course' => 'Course', 'based_on' );
>
> My test:
>
> my $search;
> push( @$search, ('me.code', { 'like', '%i1%' } ) );
> my $rs = $schema->resultset('Event')->search(
> {
> -or => $search,
> },
> {
> distinct => 1,
> prefetch => 'course',
> }
> );
> is $rs->count, 1, '$rs ok';
>
> Fails with:
>
> DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st execute
> failed: Duplicate column name 'id' [for Statement "SELECT COUNT( * ) FROM
> (SELECT me.id, me.code, me.start, me.hours, me.end, me.leader, me.based_on,
> me.attend_max, me.created_by, me.attend_min, me.RT_ticket, me.status,
> me.created, me.location, me.price_1, me.price_2, me.price_3, me.price_4,
> me.price_5, course.id, course.title, course.topics, course.tasks,
> course.prerequisites, course.overview, course.target, course.quotes,
> course.code, course.hours, course.sub_title, course.further_tasks,
> course.short_desc, course.shorter_desc FROM event me JOIN event_defaults
> course ON course.id = me.based_on WHERE ( me.code LIKE ? ) GROUP BY me.id,
> me.code, me.start, me.hours, me.end, me.leader, me.based_on, me.attend_max,
> me.created_by, me.attend_min, me.RT_ticket, me.status, me.created,
> me.location, me.price_1, me.price_2, me.price_3, me.price_4, me.price_5,
> course.id, course.title, course.topics, course.tasks, cou
rse.prerequisites, course.overview, course.target, course.quotes, course.code,
course.hours, course.sub_title, course.further_tasks, course.short_desc,
course.shorter_desc) count_subq" with ParamValues: 0='%i1%']
>
> Passes, if I comment out either "distinct" or "prefetch" but this is
> undesired.
>
> Am I doing anything wrong?
> Has this been fixed in a newer RC yet that I can test?
>
This has been fixed by accident, while doing count query optimizations. I just
shipped the 0.08103 - please give it a try and don't hesitate to report any
issues - we can always make another release. Here is the problem with mysql:
This fails with Duplicate column name 'id': (old incorrect code, would return
the count of all joined rows)
SELECT COUNT( * ) FROM (
SELECT me.id, me.name, books.id, books.source, books.owner, books.title,
books.price
FROM owners me LEFT JOIN books books ON books.owner = me.id WHERE (
books.id IS NOT NULL )
GROUP BY me.id, me.name, books.id, books.source, books.owner, books.title,
books.price
) count_subq
This passes: (new *correct* code)
SELECT COUNT( * ) FROM (
SELECT me.id
FROM owners me LEFT JOIN books books ON books.owner = me.id WHERE (
books.id IS NOT NULL )
GROUP BY me.id
) count_subq
Cheers
_______________________________________________
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]
