Btukfyl

If I have the following models how can I return all reports a user has created, but only the "highest graded" report per player?

class Player < ApplicationRecord

  has_many :reports

end



class Report < ApplicationRecord

  belongs_to :author

  belongs_to :grade

  belongs_to :player

end



class Grade < ApplciationRecord

  has_many :reports

end



class Author < ApplicationRecord

  has_many :reports

end

Example Data:

/Player/    -    /Author/   -    /Report Grade/

John Smith        -    David      -        5

John Smith        -    David      -        4

Thomas Li         -    David      -        5

Mike Lee          -    Sean       -        9

Mike Lee          -    Sean       -        2

Arnold Jackson    -    Sean       -        5

Cathleen Miller   -    Sean       -        7

Result I would like:

/Player/    -    /Author/   -    /Report Grade/

John Smith        -    David      -        5

Thomas Li         -    David      -        5

Mike Lee          -    Sean       -        9

Arnold Jackson    -    Sean       -        5

Cathleen Miller   -    Sean       -        7

Currently I'm using the following:

Report.joins(:player).where(type: %w(spring fall))

I'm unsure how to filter out the "lower graded" records. If I need to include more information please let me know.

On Postgres you can use DISTINCT ON:

class Report < ApplicationRecord

  belongs_to :player

  belongs_to :grade

  belongs_to :author



  def self.highest_graded

    Report.select(%q{

      DISTINCT ON(reports.player_id, reports.author_id)

      grades.grade AS max_grade,

      players.name AS player_name,

      authors.name AS author_name,

      reports.*

    }).joins(:player, :grade, :author)

      .order('reports.player_id, reports.author_id, grades.grade DESC')

  end

end

<table>

  <thead>

    <tr>

      <th>id</th>

      <th>Player</th>

      <th>Author</th>

      <th>Grade</th>

    </tr>

  </thead>

  <tbody>

    <% Report.highest_grade.each do |report| %>

    <tr>

      <td><%= report.id %></td>

      <td><%= report.player_name %></td>

      <td><%= report.author_name %></td>

      <td><%= report.max_grade %></td>

    </tr>

    <% end %>

  </tbody>

</table>

id  Player          Author  Grade

1   John Smith      David   5

3   Thomas Li       David   5

4   Mike Lee        Sean    9

6   Arnold Jackson  Sean    5

7   Cathleen Miller Sean    7