Rails find_by_sql returning array with empty object

Question

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:

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

I don't understand your usage here. You are enclosing the whole think in an array. Can you try just calling the find_by_sql('Select... ') without the enclosing array or the parameters at the end? — Beartech
– Beartech, Commented Feb 9, 2020 at 17:58
I need the array to bind the parameters (recipient_id, recipient_type...)(api.rubyonrails.org/classes/ActiveRecord/Querying.html). Without the array you would get an error: "wrong number of arguments (given 5, expected 1..2)" — Darkisa
– Darkisa, Commented Feb 9, 2020 at 18:06
I see what you mean about the parameters, I have not used find_by_sql where I needed to pass parameters so that threw me for a second. It feels like your query is returning some sort of an empty row instead of nil. That's why I'd run it in psql to see what you get. Also it's hard to visualize because we don't know your entire data structure. Can you post a full inspect of the empty object? — Beartech
– Beartech, Commented Feb 9, 2020 at 18:18
Weird, I can test it on one of my projects and 0 rows always gives an empty array like you are expecting. I thought that is the expected behavior too because that is what I've always gotten. Can you show some of the context of the code you originally posted? Is it doing the same thing on the Rails console? — Beartech
– Beartech, Commented Feb 9, 2020 at 18:35
That helps. And if you run just the original find_by_sql query on the Rails console it still returns an empty object? — Beartech
– Beartech, Commented Feb 9, 2020 at 18:49

Darkisa · Accepted Answer · 2020-02-09 19:09:17Z

1

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])

answered Feb 9, 2020 at 19:09

Darkisa

2,0674 gold badges26 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails find_by_sql returning array with empty object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related