Is there a more ruby way of doing this logic

Here is my controller index action

sub1 = "(select sum(courses_users.know) from courses_users where courses_users.course_id = courses.id) know_score"
sub2 = "(select levels.name from levels where levels.id = courses.level_id) level_name"
@courses = ActiveRecord::Base.connection.execute "select name,#{sub1},#{sub2} from courses order by level_id, know_score"
@total_score = User.count() * 2;

this is the view logic

<% level_name = "" %>
<% @courses.each do |course| %>
<% if course[2] != level_name %>
  <tr>
      <th colspan='2' style='text-align:left;'><br /><br /><%= course[2] %><br /><br /></th>
  </tr>
  <tr>
    <th>Title</th>
    <th>Knowledge Score</th>
  </tr>
  <% level_name = course[2] %>
<% end %>
<tr>
  <td><%= course[0] %></td>
  <td><%= (course[1].nil? ? 0 : course[1].to_i) %>/<%= @total_score %></td>
</tr>
<% end %>

this is an example of what each couse looks like in the loop

["PHP - The Basics", #<BigDecimal:1033708d0,开发者_C百科'0.31E2',9(18)>, "Beginner"]----------------

this seems like such a hack and i would love to make this more ruby like

here is my db structure if that will help

create_table "courses", :force => true do |t|
  t.string   "name"
  t.integer  "target_id"
  t.integer  "level_id"
  t.datetime "created_at"
  t.datetime "updated_at"
  t.integer  "order_num"
end

create_table "levels", :force => true do |t|
  t.string   "name"
  t.datetime "created_at"
  t.datetime "updated_at"
end

The first thing I would do is add two scopes to get the related counts from the courses table:

class Course
  has_many :courses_users
  belongs_to :level

  scope :with_know_score, joins(:courses_users).group("courses.id").select(["courses.*"," sum(courses_users.know) as know_score"])
  scope :with_level_name, joins(:level).select(["courses.*","levels.name as level_name"])
end

You can then simplify your controller code:

@courses = Course.with_know_score.with_level_name
@total_score = User.count * 2

And your view code:

<% level_name = "" %>
<% @courses.each do |course| %>
  <% if course.level_name != level_name %>
    ...
    <% level_name = course.level_name %>
  <% end %>
  <tr>
    <td><%= course.name %></td>
    <td>
      <%= course.know_score || 0 %>/<%= @total_score %>
    </td>
  </tr>
<% end %>

In the console you should be able to type:

c = Course.with_know_score.with_level_name.first
c.name # 'PHP ...'
c.level_name # 'Beginner'
c.know_score #