CODEWITHSUNDEEP
ruby-on-railsruby
Cat Named Dog
Cat Named Dog

Reputation: 1708

Order query by row from another table (ruby rails)

I have somewhat of a convoluted setup.

I am querying all users that have a thread (Conversations) of messages.

My User model has a has_many relation on two columns in Conversation

  has_many :sender_conversations, class_name: 'Conversation', foreign_key: "sender_id", dependent: :destroy
  has_many :recipient_conversations, class_name: 'Conversation', foreign_key: "recipient_id", dependent: :destroy

I needed a way to get the latest conversation since any user can have multiple threads.

To achieve this, I have the following methods on the user model:

 def conversations
   sender_conversations.or(recipient_conversations)
  end

  def latest_conversation
    conversations.order(updated_at: :desc).first
  end

Conversation model:

class Conversation < ApplicationRecord
  ...

  belongs_to :sender, foreign_key: :sender_id, class_name: 'User'
  belongs_to :recipient, foreign_key: :recipient_id, class_name: 'User'

  ...

  validates :sender_id, uniqueness: { scope: [:recipient_id, :property_id] }

  scope :between, ->(sender_id, recipient_id) do
    where(sender_id: sender_id, recipient_id: recipient_id).or(
      where(sender_id: recipient_id, recipient_id: sender_id)
    )
  end
...

The controller action is querying users that have either a sender_id or recipient_id

def users_with_existing_conversations
    authorize! :users_with_existing_conversations, Conversation

    @users = User.accessible_by(current_ability, :index_conversations)

    @users = @users.where(id: Conversation.select(:sender_id))
                    .or(@users.where(id: Conversation.select(:recipient_id)))


    ...




  end

Then finally, in the view, I'm looping through

<% @users.each do |user| %>
  <tr class="table__row" onclick="window.location.href = '/conversations?user_id=<%= user.id %>'">
    <td><%= user.name %>(<%= user.id %>)</td>
    <td><%= user.surname %></td>
    <td><%= user.email %></td>
    <td><%= user.company_name.present? ? user.company_name : "N/A" %></td>
    <td><%= user.role.capitalize %></td>
    <td><%= user.created_at.try(:strftime, '%b %d, %Y') %></td>
    <td><%= user.latest_conversation.updated_at.localtime.strftime('%b %d, %Y at %l:%M %p') %></td>
    <td class="table__more">
      <%= link_to "Show details", conversations_path(user_id: user.id), class: 'table__row__details button button--tertiary button--tertiary-small' %>
    </td>
  </tr>
  <% end %>

Now what I can't figure out is how to order these rows by the latest_conversation which is the updated_at column.

So I have a users table I'm querying, but I want to order the query by their relation with the conversations table's updated_at column.

Upvotes: 1

Views: 69

Answers (1)

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33481

You can try with a custom INNER JOIN using the IN clause to check both conversations' columns:

User.joins('INNER JOIN conversations c ON users.id IN (c.sender_id, c.recipient_id)')
    .order('c.created_at')

Upvotes: 1

Related Questions