Advance query in rails especially with condition on associated/joined table

Question

I have seen looking everywhere for a solution and many time feeling that this is what I was looking for only to learn I need to keep looking so finally here it is...

I have a model Person:

class Person < ActiveRecord::Base


  has_many :sent_messages, :class_name => 'Message', :inverse_of => :sender
  has_many :received_messages, :class_name => 'Message', :inverse_of => :receiver

  # ... ... ... 

end

And a model Message

class Message < ActiveRecord::Base


  belongs_to :sender, :class_name => 'Person', foreign_key => 'sender_id'
  belongs_to :receiver, :class_name => 'Person', foreign_key => 'receiver_id'

  # ... ... ...

end 

Now I have to search the messages table for the input in search field for all those messages whose message body(it is a column in messages table for Message model which contain only text) matches with input search string or with last_name(in sender of message, last_name is column in people table for Person model) of sender. I tried various ways but nothing worked and when I saw this link I thought this is it. This is what I have been looking for but alas this too rejected me and spewed an error, which is listed with query.

@messages = Message.includes(:sender).
                    where("UPPER(body) like UPPER(?) or 
                    UPPER(sender.last_name) like UPPER(?)", 
                    "%#{"sus"}%", "%#{"sus"}%").
                    references(:people)

In references i gave <:people> as given link suggested to give the name of actual table although I tried <:sender> as well that didn't work as expected.
The above query in irb console gave following error:-

undefined method `references' ActiveRecord::Relation
PG::UndefinedTable: ERROR:  missing FROM-clause entry for table

I even tried this link of stackoverflow but this didn't work as well.

So, please help me out here... What am I missing??

Note:- I am using PostgreSQL for my database.

Answer 1

Not sure why you're having trouble. Does the below produce an error? Do you just want to check sender.last_name and not receiver.last_name, too?

Message.includes(:sender)
       .where("UPPER(body) LIKE UPPER(?) OR 
               UPPER(people.last_name) LIKE UPPER(?)", 
               "%#{"sus"}%", "%#{"sus"}%")

Couple of notes. I've used senderS (plural). If that doesn't work, find out what alias is being used on includes(:sender) using Message.includes(:sender).to_sql.

I Have no idea what %#{"sus"}% is (could you explain?), and LIKE %% is a very expensive query. Look into full-text search with postgres.