CODEWITHSUNDEEP
sqlruby-on-railsrails-activerecord
DaniG2k
DaniG2k

Reputation: 4893

Better way of looping over a SQL result set

I'm just wondering if there's a better way to perform a SQL query over another SQL result set that I'm storing in an instance variable.

My code looks like this:

def index
  id = current_user.id.to_s

  #First SQL query
  @messages = Message.where("user_id = ? OR recipient_id = ?", id, id).group('recipient_id')
end

And in the view I have

<% @messages.each do |m| %>
<!-- Second SQL query -->
<% r = Tutor.find(m.recipient_id).user %>
  <%= "#{r.first_name + ' ' + r.last_name}" %><br />
  <%= m.subject %><br />
  <%= m.body %><br />
  <%= m.user_id %><br />
  <%= m.recipient_id %><br />
<% end %>

I'm looping over @messages, and then doing a SQL query on each of those objects as well and I'm wondering if there's a better (smarter) way of doing this? Thanks!

Upvotes: 3

Views: 729

Answers (1)

Marcelo De Polli
Marcelo De Polli

Reputation: 29291

You need an ActiveRecord association between Message and Tutor. I would need to take a look at your models to say exactly how to relate them, but you're basically doing the association work yourself instead of offloading it to AR/SQL.

Once you have the proper relationship in place, you would see something along these lines.

In your controller:

def index
  id = current_user.id
  @messages = Message.includes(:tutor).where("user_id = ? OR recipient_id = ?", id, id).group(:recipient_id)
end

In your view:

<% @messages.each do |m| %>
  <%= m.tutor.user.to_s %><br />
  <%= m.subject %><br />
  <%= m.body %><br />
  <%= m.user_id %><br />
  <%= m.recipient_id %><br />
<% end %>

In your User model:

class User < ActiveRecord::Base
  def to_s
    "#{first_name} #{last_name}"
  end
end

Upvotes: 2

Related Questions