Guilherme Nazareth de Souza Guilherme Nazareth de Souza - 6 months ago 51
Ruby Question

Rails - Searching for messages and conversations. How to show only the latest message of each conversation?

I created a messaging system for my Rails app using this tutorial: Create a Simple Messaging System on Rails, by Dana Mulder. It works wonders, but I am having a problem in tweaking it a little bit. Would anyone have insight in creating a conversations inbox that shows the latest message of each conversation? More details follow.

There are two models in the system: Conversation and Message:


  • A Conversation has a user1_id and a user2_id.

  • A Message has a user_id, body, and conversation_id.



Thus, each conversation can have multiple messages, and each message belongs to the user who sent it.

I built a ConversationsController that shows all messages in the index. What I want, though, is one that only shows the latest message of each conversation, in order.

I'm going to paste here the current version of the Conversations#Index action, and the one I'm envisioning (which creates a mistake that I'll describe below).

Working Conversations#Index (displays all messages):

@conversations = Conversation.where("user1_id = #{current_user.id} OR user2_id = #{current_user.id}")

@array = []
@conversations.each do |item|
@array.push(item.id)
end
@messages = Message.where("conversation_id in (?)", @array)


Not working Conversations#Index (SHOULD display only latest message of each conversation:

@conversations = Conversation.where("user1_id = #{current_user.id} OR user2_id = #{current_user.id}").order("updated_at DESC")

@array = []
@conversations.each do |item|
@array.push(item.id)
end

@list = []
@array.each do |i|
@id = Message.where("conversation_id = (?)", i).last
@list.push(@id.id)
end

@messages = Message.where("id in (?)", @list)


When I enter the latter piece of code, the inbox shows no messages at all. Any ideas why?

Logs

Started GET "/conversations" for 99.234.104.113 at 2016-05-14 04:19:05 +0000
Processing by ConversationsController#index as HTML
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1 [["id", 1]]
Conversation Load (0.3ms) SELECT "conversations".* FROM "conversations" WHERE (1 IN (sender_id, recipient_id)) ORDER BY updated_at DESC
Message Load (0.4ms) SELECT "messages".* FROM "messages" WHERE (conversation_id = 2) ORDER BY "messages"."id" DESC LIMIT 1
Message Load (0.3ms) SELECT "messages".* FROM "messages" WHERE (conversation_id = 1) ORDER BY "messages"."id" DESC LIMIT 1
(0.2ms) SELECT COUNT(*) FROM "conversations" WHERE (1 IN (sender_id, recipient_id))
Conversation Load (0.3ms) SELECT "conversations".* FROM "conversations" WHERE (1 IN (sender_id, recipient_id)) ORDER BY updated_at DESC LIMIT 10 OFFSET 0
User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1 [["id", 3]]
User Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1 [["id", 2]]
Rendered conversations/_convo.html.erb (150.4ms)
Message Load (0.3ms) SELECT "messages".* FROM "messages" WHERE (id in (10,9)) LIMIT 10 OFFSET 0
Rendered conversations/_message.html.erb (1.0ms)
Rendered conversations/index.html.erb within layouts/application (157.4ms)
Rendered layouts/_shim.html.erb (0.1ms)
Rendered layouts/_header.html.erb (1.6ms)
Rendered layouts/_footer.html.erb (0.5ms)
Completed 200 OK in 299ms (Views: 292.1ms | ActiveRecord: 2.3ms)

Answer

You have the wrong field names in the query. Try this:

@conversations = Conversation.
  where("user1_id = #{current_user.id} OR user2_id = #{current_user.id}").
  order("updated_at DESC")

This should get you going with that query. It's always good to show the console log for something like this, as well, so that the execution of the action can be followed.

To make an improvement on your query, you can use query parameters. They let you include the values that you want without using string interpolation (which could potentially lead to SQL injection issue). You can do something like this:

@conversations = Conversation.
  where("user1_id = ? OR user2_id = ?", current_user.id, current_user.id).
  order("updated_at DESC")

And, you can shorten the same query just a bit by using an IN operator and taking fewer parameters:

@conversations = Conversation.
  where("? IN (user1_id, user2_id)", current_user.id).
  order("updated_at DESC")

For the rest of your method, here are a few changes that might improve the performance and reduce the code a bit:

@messages = Message.
  where(conversation_id: @conversations).
  group(:conversation_id).
  having("id = MAX(id)")

The first line builds an array of item.ids from all of the conversations in @conversations. This uses the Ruby Array#map method to accomplish the task efficiently.

The second line uses hash arguments to where to achieve a tighter query by using ActiveRecord to generate a SQL IN clause (See the Hash Conditions section of the Active Record Query Interface for more information). It then groups the messages by conversation_id and filters the latest with a having clause.

Also note that you don't have to use @ variables unless you're communicating them implicitly elsewhere, such as to the view or other methods in the same class. Local variables can just have regular names like array and messages.