CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
jaqbyte
jaqbyte

Reputation: 57

:order by has_many association latest entry? how?

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

Answers (1)

tamersalama
tamersalama

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

Related Questions