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

[Peter Rabbitson]

A Murka wrote:
> 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.
> 

So any chance I can get a test for this?