Reputation: 57
rails is so much fun until you need to find a solution and it takes three days with no luck! ;-(
How do I order by the latest entry in has_many association for each entry?
Explanation:
I do a ranking of a Model Video
by an associated Model Score
(with a field ranking_score
). The database is a postgreSQL.
Because I will frequently add a score for a video I need to order the videos by the latest score entry for each video.
Video.all(:include => :scores, :order => "scores.ranking_score DESC")
...works as expected until I add a new score entry for a video (I don't update, because I will have statistics on the ranking).
i also tried with all varieties (DESC & ASC) and I reordered the priorities but without luck:
Video.all(:include => :scores, :order => "scores.created_at DESC, scores.ranking_score")
and
Video.all(:include => :scores, :order => "scores.ranking_score, scores.created_at")
and so on.
I searched the web, tried to figure out postgreSQL commands and so on. But beeing a total noob (on rails for 6 weeks now) I need help, please.
Here is my code:
Video Model:
class Video < ActiveRecord::Base
.
has_many :scores
.
end
Score Model:
class Score < ActiveRecord::Base
attr_accessible :ranking_score, :video_id
belongs_to :video, :foreign_key => :video_id
def position
self.class.count( :conditions => ['ranking_score >= ?', self.ranking_score])
end
end
Video Controller:
class VideosController < ApplicationController
def index
setup_videos
@video ||= Video.new
respond_to do |format|
format.html # show.html.erb
format.json { render json: @video }
end
end
.
private
def setup_videos
@videos = Video.all(:include => :scores, :order => "scores.ranking_score DESC")
@user = current_user
end
end
As you can see to verify the ranking I included a method position, because I need a user specific view where only user videos get listed, but with the overall ranking shown (different problem, because like this it does not work with multiple entries for each video)
_ranking.html.erb (rendered whithin "index") --- without HTML Tags:
<% @videos.each.with_index do |video, index| %>
<%= index.+1 %>
ranking <%= video.scores.last.ranking_score %>
postition <%= video.scores.last.position %>
<% end %>
<% @user.videos.count %>
Thank you for your time! joh
Upvotes: 0
Views: 322
Reputation: 4143
I'm having troubles understanding your requirements. However - have you thought of caching the highest rank with each of the video records?
You could add another column in video called highest_ranking
that gets updated in an Score after_save operation or similar (depending on the score update frequency).
This will save you the trouble of joining to scores to order the video.
Upvotes: 1