includes and eager loading not working in simple setup

Hi,

I'm having trouble with a simple users-messages setup, where users can
send messages to other users. Here's my class definition:

class Message < ActiveRecord::Base
  belongs_to :from_user, :class_name => 'User'
  belongs_to :to_user, :class_name => 'User'
end

class User < ActiveRecord::Base
  def messages(with_user_id)
    Message.where("from_user_id = :id AND to_user_id = :with_user_id
OR to_user_id = :id AND from_user_id = :with_user_id",
      { :id => id, :with_user_id => with_user_id })
      .includes(:from_user, :to_user)
      .order("created_at DESC")
  end
end

When I do some_user.messages(another_user.id), I want to retrieve the
conversation that some_user has with another_user. I do get back an
array of Messages, but it doesn't include the eager loading of
from_user and to_user. What am I doing wrong? Am I forced to use
joins?

Thanks,
J

Hi,

I'm having trouble with a simple users-messages setup, where users can
send messages to other users. Here's my class definition:

class Message < ActiveRecord::Base
belongs_to :from_user, :class_name => 'User'
belongs_to :to_user, :class_name => 'User'
end

class User < ActiveRecord::Base
def messages(with_user_id)
Message.where("from_user_id = :id AND to_user_id = :with_user_id
OR to_user_id = :id AND from_user_id = :with_user_id",
{ :id => id, :with_user_id => with_user_id })
.includes(:from_user, :to_user)
.order("created_at DESC")
end
end

When I do some_user.messages(another_user.id), I want to retrieve the
conversation that some_user has with another_user. I do get back an
array of Messages, but it doesn't include the eager loading of
from_user and to_user.

How many SQL queries do you see in the log for

messages = some_user.messages(another_user.id)

(I believe you should see 3 queries if from_user and to_user data
is present).

When asking later on:

messages.first.from_user

is it then launching an SQL for the individual from_user
at that time?

What am I doing wrong? Am I forced to use
joins?

It must be possible to get this working with .includes
as you tried.

HTH,

Peter

Hi Peter,

How did you know that?! Yes, indeed there are 3 SQL queries:

Message Load (0.5ms) SELECT `messages`.* FROM `messages` WHERE
(from_user_id = 3 AND to_user_id = '2' OR to_user_id = 3 AND
from_user_id = '2') ORDER BY created_at DESC LIMIT 1
User Load (0.4ms) SELECT `users`.* FROM `users` WHERE (`users`.`id` =
2)
User Load (0.5ms) SELECT `users`.* FROM `users` WHERE (`users`.`id` =
3)

Why is that? I really expected one SQL statement. In fact I need one
SQL statement since I need to serialize and respond to in JSON. How
can I fix it?

Thanks so much,
Jenna

Hi Peter,

How did you know that?! Yes, indeed there are 3 SQL queries:

Message Load (0.5ms) SELECT `messages`.* FROM `messages` WHERE
(from_user_id = 3 AND to_user_id = '2' OR to_user_id = 3 AND
from_user_id = '2') ORDER BY created_at DESC LIMIT 1
User Load (0.4ms) SELECT `users`.* FROM `users` WHERE (`users`.`id` =
2)
User Load (0.5ms) SELECT `users`.* FROM `users` WHERE (`users`.`id` =
3)

Why is that? I really expected one SQL statement. In fact I need one
SQL statement since I need to serialize and respond to in JSON. How
can I fix it?

This is how include works by default. The loaded users are wired up to
the message objects for you. There is one query per association, so
you get the most benefit when there are multiple messages. The fact
that it takes multiple queries (or even whether you use :include at
all) has no effect on what the serialised json will look like.

You can force AR to use a joins based include strategy via eager_load
if you really want it to. (but like i said whether stuff is eagerly
loaded or not doesn't impact the generated json)

Fred

Ok... I had no idea. I always thought that if you have an
includes,that will automatically resolve to a join because otherwise
this might result in the N+1 problem. I guess I need to read up about
it some more.

One final question: when I return some_user.messages(another_user.id)
to json from my controller, I don't get the from_user and the to_user:
format.json { render :json => { :conversation => @messages.as_json } }
returns
{"conversation":[{"message":
{"created_at":"2012-02-25T20:21:01Z","from_user_id":1,"id":
2,"message":"lion king","to_user_id":3,"updated_at":null}},{"message":
{"created_at":"2012-02-25T20:21:01Z","from_user_id":3,"id":
9,"message":"this is ninth","to_user_id":1,"updated_at":null}}]}

How can I force the from_user and to_user as part of each "message"?
(I guess I could have them returned as separate tags at the same level
as "conversation", but I was wondering if there's a way to do it
inside "message", e.g. {"message":
{created_at:...","id":"1","from_user":{"name":"Bob","age":"20",
etc.}}})

Thanks so much for all the help!

I think you can use the :include option of render :json to do that.

Colin

Great! Thanks everyone for the help.