CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Darkisa
Darkisa

Reputation: 2037

Rails find_by_sql returning array with empty object

I am using find_by_sql to do a query on my Conversationalist model using Postgres as the DB server:

Conversationalist.find_by_sql(['
  SELECT * FROM (
    SELECT * FROM conversationalists
    WHERE conversable_id = ? AND conversable_type = ?
  ) t1 
  LEFT JOIN (
    SELECT * FROM conversationalists 
    WHERE conversable_id = ? AND conversable_type = ?
  ) t2 
  ON t1.chat_id = t2.chat_id',
  recipient_id, recipient_type, sender_id, sender_type])

It works fine if there is a result. But if there is no result then I get an array with an empty Conversationalist object: [#<Conversationalist id: nil, conversable_type: nil...>]

Here is what I get as a result doing a direct query on the DB:

enter image description here

What I am expecting is an empty array since no rows should be returned but instead I get a result. How would I get an empty array if no results are returned?

ADDITIONAL CONTEXT

What I am trying to do is essentially a chat. When someone messages another user, the code above first checks to see if those two people are already chatting. If they are the message gets added to the chat. If not, a new Chat gets created and the message gets added:

class MessagesController < ApplicationController
  def create
    message = new_message
    conversation = already_conversing?
    if conversation.empty? || conversation.first.id.nil?
      chat = Chat.new
      chat.messages << message
      chat.conversationalists << sender
      chat.conversationalists << recipient
      chat.save!
    else
      chat = Chat.find(conversation.first.chat_id)
      chat.messages << message
    end
    head :ok
  end

  private

  def new_message
    Message.new(
      sender_id: params[:sender_id], 
      sender_type: params[:sender_type],
      recipient_id: params[:recipient_id],
      recipient_type: params[:recipient_type],
      message: params[:message] 
    )
  end

  def already_conversing?
    Conversationalist.conversing?(
      params[:recipient_id], 
      params[:recipient_type], 
      params[:sender_id], 
      params[:sender_type]
    )
  end
end

The Model:

class Conversationalist < ApplicationRecord
  def self.conversing?(recipient_id, recipient_type, sender_id, sender_type)
    Conversationalist.find_by_sql(['
      SELECT * FROM (
        SELECT * FROM conversationalists
        WHERE conversable_id = ? AND conversable_type = ?
      ) t1 
      LEFT JOIN (
        SELECT * FROM conversationalists 
        WHERE conversable_id = ? AND conversable_type = ?
      ) t2 
      ON t1.chat_id = t2.chat_id',
      recipient_id, recipient_type, sender_id, sender_type])
  end
end

Upvotes: 0

Views: 229

Answers (1)

Darkisa
Darkisa

Reputation: 2037

So I was able to figure it out with the help of @Beartech from the comments above. Essentially the issue was happening because of the LEFT JOIN. If there are any results in t1 then Rails returns an array with an empty object. Similarly, if it was a RIGHT JOIN and t2 had a result, Rails would do the same. So the fix, in order to get an empty array, is to change the join to an INNER JOIN:

Conversationalist.find_by_sql(['
  SELECT * FROM (
    SELECT * FROM conversationalists
    WHERE conversable_id = ? AND conversable_type = ?
  ) t1 
  INNER JOIN (
    SELECT * FROM conversationalists 
    WHERE conversable_id = ? AND conversable_type = ?
  ) t2 
  ON t1.chat_id = t2.chat_id',
  recipient_id, recipient_type, sender_id, sender_type])

Upvotes: 1

Related Questions