SQL .where query generating weird results (Rails)

Question

I am using a simple search to query the database.

In model.rb I have:

# condition for the search query [insert columns to iterate through]
    def self.search(search)
        if search
             where(["bible_no || sample_no || location || experiment_details || sampled_type || sampled_by || date || file LIKE ?", "%#{search}%"]) 

        else
            all
        end

    end

And in my controller.rb I have:

def index
 # checks if a user is logged in
     if current_user 
          if params[:search].present?
            @bibles = Bible.search(params[:search]).paginate(:page => params[:page]) 
          else
            @bibles = Bible.all.paginate(:page => params[:page])
          end
        # the index for the public view
         else 
          if params[:search].present?
           @bibles = Bible.all.where("location LIKE '%TULLOCH %'").search(params[:search]).paginate(:page => params[:page])
          else
           @bibles = Bible.all.where("location LIKE '%TULLOCH %'").paginate(:page => params[:page])
          end 
        end
end

The reason for this logic is that I wanted to further filter the view available to the public (non-logged-in user) to only show rows in which the "location" column contains "TULLOCH". Everything works fine at filtering the index page for public view except that when you actually perform a search, the results miss some of the rows containing the search term. For instance, a search for "100" completely misses like 90% of all occurrences of "100" in the "bible_no" column attribute although there are many of them, but strangely finds same occurrences in "file" column attribute.

I had earlier done the filtering part using a view file _partial (which worked fine) but saw that as messy as it produces two views and extra logic in the index view.

The routes and search_field in the view are just fine. I don't think I need to paste them here.

Any ideas?

Prince Bansal · Accepted Answer · 2018-08-05 09:33:11Z

1

Below code may help you.

where("bible_no LIKE :search_query OR sample_no LIKE :search_query OR location LIKE :search_query OR experiment_details LIKE :search_query OR sampled_type LIKE :search_query OR sampled_by LIKE :search_query OR date LIKE :search_query OR file LIKE :search_query", search_query: "%#{search}%")

Be careful with date - if you try to search a string that is not a valid date then it will raise date parse error. If type of date is string then it will work with any error.

answered Aug 5, 2018 at 9:33

Prince Bansal

2862 silver badges5 bronze badges

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

1 Comment

Chidozie Nnachor Over a year ago

Thank you for the answer. It works just fine and expected, although @nick above explained the WHY. Thanks again.

Nick · Accepted Answer · 2018-08-05 05:43:25Z

1

Your problem is that LIKE has higher precedence than ||, so your WHERE condition is actually equivalent to this:

bible_no || sample_no || location || experiment_details || sampled_type || sampled_by || date || (file LIKE ?)

But I think what you actually want is

bible_no LIKE ? || 
sample_no LIKE ? || 
location LIKE ? || 
experiment_details LIKE ? ||
sampled_type LIKE ? ||
sampled_by LIKE ? ||
date LIKE ? ||
file LIKE ?

Note that to use this query you will need to bind the search value 8 times instead of just once.

answered Aug 5, 2018 at 5:43

Nick

147k23 gold badges67 silver badges106 bronze badges

3 Comments

Chidozie Nnachor Over a year ago

Thanks for your answer. You are right about what I actually wanted. I did not know || and LIKE had a pecking order in execution.

Chidozie Nnachor Over a year ago

I am torn between which of the two between your answer and that of @prince-bansal below to accept as the answer. While you explained why my query won't work and gave me tips to figure it out (which is not bad at all) , he gave me a working solution. Let me crave your indulgence and accept his (since he needs the points more :) ).

Nick Over a year ago

No problem at all. A working solution is better than pointers toward one.

Collectives™ on Stack Overflow

SQL .where query generating weird results (Rails)

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related