CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4join
stefano_cdn
stefano_cdn

Reputation: 1382

Rails 4 Conversation Model JOIN Table with condition

I have a Conversation model with

recipient_id
sender_id

Fetching the current user's conversations looks like

conversations = Conversation.where("conversations.sender_id =? OR conversations.recipient_id =?",current_user.id,current_user.id)

Let's call other_user the user in the conversation who is not the current_user.

Since the current user can either be the sender or the recipient, it's not possible to use a JOIN or Include on the users table to fetch the "other user" involved in the conversation. Thus, rendering the conversations creates a N+1 situation, issuing 1 query for each other user.

Is there a way to fetch the other_user using an includes or a JOIN, with a condition basically saying:

if current_user_id == recipient_id JOIN on recipient_id
if current_user_id == sender_id JOIN on sender_id

Upvotes: 0

Views: 75

Answers (1)

David Runger
David Runger

Reputation: 1294

If you just need certain attributes of the other_user, such as the username, then something like this should work:

conversations = Conversation.find_by_sql([<<-SQL, current_user.id, current_user.id, current_user.id])
  SELECT
    convos_involving_user.*
  FROM
    -- We'll use UNION to create a table that joins together the users and
    -- conversations tables based on both sender and recipient. Each
    -- conversation will be represented by two rows in this joined table:
    -- one row joining that conversation to the sender's user info, another
    -- row joining that conversation to the recipient's user info.
    (
    -- join conversations and users based on sender
    SELECT
      conversations.*, users.id AS user_id, users.username AS other_user
    FROM
      conversations
    INNER JOIN
      users ON users.id = conversations.sender_id
    UNION
    -- join conversations and users based on recipient
    SELECT
      conversations.*, users.id AS user_id, users.username AS other_user
    FROM
      conversations
    INNER JOIN
      users ON users.id = conversations.recipient_id
    ) AS convos_involving_user
  -- Now that we conceptually have that big join table with two rows for
  -- each conversation, we filter that table. First, we are only interested
  -- in the conversations where either the recipient or sender is the
  -- current user. But we still have duplicate rows for each message, so we
  -- also filter out the rows that join a conversation rows to the
  -- current_user's row. This leaves, for each conversation involving the
  -- current user, the row that links the conversation to the other user.
  WHERE
    (recipient_id = ? OR sender_id = ?) AND user_id != ?
SQL

Then each of the conversations will have an other_user method that you can call on it to get the username of the other user. You can use this in your view or whatnot. To illustrate using the other_user method:

conversations.each { |convo| puts convo.other_user }

Hope this helps.

Upvotes: 1

Related Questions