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

[Peter Rabbitson]

On Fri, Jul 10, 2009 at 12:08:56AM -0400, 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:
> 
> <snipped chained join example>
> 
> 
> 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.
> 

The join part seems like a bug to me... possibly an untested regression
I introduced while playing with the join handling. Please submit a test
patch, adding a couple of tests demonstrating this behavior against [1]
I'd say a good place would be to augment the last test block on line
122 of t/90join_torture.t

Now the WHERE is a diferent story - the arguments are not (and probably
never will be) adjusted. Remember that in a chained search you might
(and are in fact more likely to) be adding more WHERE conditions to your
first join instead of what came in next. DBIC does the right thing and
does not attempt to guess for you. The WHERE condition adjstment is
solely your responsibility.

Cheers

[1] http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/