| CC: | paulm [...] paulm.com |
| Subject: | prefetch and group_by not playing nicely together |
Here is the Perl data structure of the query I would like:
join => [qw/publication_story/],
select => [{ count => 'publication_story.uid' }, qw/me.uid me.name me.short_name
me.url me.description me.publication_type_uid me.region_uid me.channel_type_uid
me.circulation me.ave me.comments/,],
as => [qw/story_count uid name short_name url description publication_type_uid
region_uid channel_type_uid circulation ave comments/],
group_by => [qw/me.uid/],
prefetch => [qw/publication_type region/],
If I comment-out the 'group_by', I get the sensible:
SELECT COUNT( publication_story.uid ), me.uid, me.name, me.short_name, me.url,
me.description, me.publication_type_uid, me.region_uid, me.channel_type_uid,
me.circulation, me.ave, me.comments, publication_type.uid,
publication_type.parent_uid, publication_type.value, publication_type.hide,
publication_type.alias, publication_type.order_priority,
publication_type.show_in_lists, publication_type.data_type,
publication_type.cascade_data_type, publication_type.description, region.uid,
region.parent_uid, region.value, region.hide, region.alias,
region.order_priority, region.show_in_lists, region.data_type,
region.cascade_data_type, region.description FROM publication me LEFT JOIN story
publication_story ON publication_story.publication_uid = me.uid LEFT JOIN
code_tree publication_type ON publication_type.uid = me.publication_type_uid
LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( ( name LIKE
'%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) ORDER BY name;
This doesn't work though, as the count requires a GROUP BY clause. If I manually
add that GROUP BY clause to the end ("GROUP BY me.uid") it works fine. However,
if I ask DBIx::Class to do it, it gets all messed up:
SELECT me.story_count, me.uid, me.name, me.short_name, me.url, me.description,
me.publication_type_uid, me.region_uid, me.channel_type_uid, me.circulation,
me.ave, me.comments, publication_type.uid, publication_type.parent_uid,
publication_type.value, publication_type.hide, publication_type.alias,
publication_type.order_priority, publication_type.show_in_lists,
publication_type.data_type, publication_type.cascade_data_type,
publication_type.description, region.uid, region.parent_uid, region.value,
region.hide, region.alias, region.order_priority, region.show_in_lists,
region.data_type, region.cascade_data_type, region.description FROM (SELECT
COUNT( publication_story.uid ) AS story_count, me.uid, me.name, me.short_name,
me.url, me.description, me.publication_type_uid, me.region_uid,
me.channel_type_uid, me.circulation, me.ave, me.comments FROM publication me
LEFT JOIN story publication_story ON publication_story.publication_uid = me.uid
WHERE ( ( name LIKE '%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) GROUP BY me.uid ORDER
BY name) me
LEFT JOIN story publication_story ON publication_story.publication_uid = me.uid
LEFT JOIN code_tree publication_type ON publication_type.uid =
me.publication_type_uid LEFT JOIN code_tree region ON region.uid = me.region_uid
WHERE ( ( name LIKE '%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) ORDER BY name;
The work-around I'm using at the moment is to remove the 'pre-fetch', which
gives the following SQL:
SELECT COUNT( publication_story.uid ), me.uid, me.name, me.short_name, me.url,
me.description, me.publication_type_uid, me.region_uid, me.channel_type_uid,
me.circulation, me.ave, me.comments FROM publication me LEFT JOIN story
publication_story ON publication_story.publication_uid = me.uid WHERE ( ( name
LIKE '%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) GROUP BY me.uid ORDER BY name;
Please let me know if you'd like any more details.