'find_with_reputation' for multiple reputations?

[ericpeters0n]

I'm trying to pull records that have multiple reputation types e.g. retrieve Books with both avg_rating and num_readers

my_books = Book.find_with_reputation(:avg_rating, :all, {:order => 'avg_rating DESC'})
my_books = Book.find_with_reputation(:num_followers, :all)

...such that a Book object has both an avg_rating and num_followers

my_books[0].avg_rating    # => avg_rating
my_books[0].num_followers # => num_followers

(Note: I need to generate a large array of Books with these properties for consumption by another endpoint, so calling 'get_reputation_for' individually for each instance isn't practical --or likely performant.)

But it's obviously preferable to do this in one transaction, and not have to do additional sorting/ordering & addition of attributes in Rails... So what I'd like is to do something like:

Book.find_with_reputation(:avg_rating, :num_followers, :all, {:order => 'avg_rating DESC'})

**Is there an efficient way to do this?

Digging into the SQL,

Book.find_with_reputation_sql(:avg_rating, :all, {:order => 'avg_rating DESC'})

generates

"SELECT books.*, COALESCE(rs_reputations.value, 0) AS avg_rating FROM \"books\" LEFT JOIN rs_reputations ON books.id = rs_reputations.target_id AND rs_reputations.target_type = 'Book' AND rs_reputations.reputation_name = 'avg_rating' AND rs_reputations.active = 't' ORDER BY avg_rating DESC"

So it appears that what I need is 2 LEFT JOINs on rs_reputations, one for avg_rating, and one for num_followers... but this is beyond my SQL powers, and there may be a better way to approach this. Suggestions?

[ericpeters0n]

Update:

This SQL does the job, but I'm not clear on how to best add this functionality to the finder_methods

SELECT books.*, COALESCE(rs_reputations.value, 0) AS avg_rating, COALESCE(rs_reputations2.value,0) AS karma FROM \"books\" LEFT JOIN rs_reputations ON books.id = rs_reputations.target_id AND rs_reputations.target_type = 'Book' AND rs_reputations.reputation_name = 'avg_rating' AND rs_reputations.active = 't' LEFT JOIN rs_reputations AS rs_reputations2 ON books.id = rs_reputations2.target_id AND rs_reputations2.target_type = 'Book' AND rs_reputations2.reputation_name = 'karma' AND rs_reputations2.active = 't' ORDER BY karma DESC

or, in Rails:

.select("books.*, COALESCE(rs_reputations.value, 0) AS avg_rating, COALESCE(rs_reputations2.value,0) AS karma")
.joins("LEFT JOIN rs_reputations ON books.id = rs_reputations.target_id AND rs_reputations.target_type = 'Book' AND rs_reputations.reputation_name = 'avg_rating' AND rs_reputations.active = 't'")
.joins("LEFT JOIN rs_reputations AS rs_reputations2 ON books.id = rs_reputations2.target_id AND rs_reputations2.target_type = 'Book' AND rs_reputations2.reputation_name = 'karma' AND rs_reputations2.active = 't'")