[Dbix-class] Chaining searches with joins on the same table

[A Murka]

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