Here's my issue: I have a table of 'images', which has_many
'ratings', each of which belongs_to a 'user'. I want to get:
1. Aggregate sum of all ratings for each object, as well as
2. the logged-in user's rating for each object.
I have extended ResultSet::Image to have 'with_scores' and
'with_scores_for_user' methods, each of which works individually. They
both join on the ratings table, though, and when I chain them as in
$images->with_scores->with_scores_for_user($user), it only produces
ONE join instead of the two that are necessary.
Here's the code for the methods:
package PicDB::ResultSet::Image;
use strict;
use warnings;
use base 'PicDB::ResultSet';
sub with_scores {
my ($self) = @_;
return $self->search({},
{
join => ['ratings'],
'+select' => [
\'SUM(CASE WHEN ratings.score = 1 THEN
1 ELSE 0 END) AS upvotes',
\'SUM(CASE WHEN ratings.score = -1 THEN
1 ELSE 0 END) AS downvotes',
\'SUM(ratings.score) AS total_score',
\'COUNT(*) AS num_votes',
],
'+as' => [ 'upvotes', 'downvotes',
'total_score', 'num_votes' ],
group_by => ['me.id'],
}
);
}
sub with_scores_for_user {
my ($self, $user) = @_;
return $self;
return $self->search({'ratings.user_id' => $user->id},
{
join => ['ratings'],
'+select' => ['ratings.score AS user_score'],
'+as' => ['user_score'],
}
);
}
1;
Here is the output using DBIC_TRACE=1 for
$images->with_scores->with_scores_for_user($user).
The ->with_scores_for_user($user) call adds the <bracketed> sections:
SELECT me.id, me.srcurl, me.caption, me.user_id, me.ip,
me.when_uploaded, me.views,
SUM(CASE WHEN ratings.score = 1 THEN 1 ELSE 0 END) AS upvotes,
SUM(CASE WHEN ratings.score = -1 THEN 1 ELSE 0 END) AS downvotes,
SUM(ratings.score) AS total_score,
COUNT(*) AS num_votes,
< ratings.score AS user_score >
FROM image me LEFT JOIN rating ratings ON ratings.image_id = me.id
< WHERE ( ratings.user_id = ? ) >
GROUP BY me.id ORDER BY when_uploaded DESC LIMIT 12: '7'
As you can see, this query should have joined on 'ratings' -twice-,
and the condition should have been WHERE (ratings_2.user_id = ?). I
feel that DBIC should somehow recognize that chaining the two queries
should create two joins.
Do I just have to create a method that does both joins at once? Is a
better way to implement these aggergates?
Thanks,
-amurka
_______________________________________________
List:
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-classIRC: irc.perl.org#dbix-class
SVN:
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/Searchable Archive:
http://www.grokbase.com/group/dbix-class@...
