can you use activerecord to find substring of a field? (quick & dirty keyword finder)

rails sql query
rails find all
rails find by attribute
rails where or condition
rails where contains
rails search query
activerecord where
rails where and where not

Suppose a database contains a field 'keywords' and sample records include: "pipe wrench" "monkey wrench" "crescent wrench" "crescent roll" "monkey bars"

is there a way in activerecord to find the records where the keyword field contains the substring "crescent"?

(It's just a quick and dirty lookup for a quick concept prototype)

Yeah, just use a LIKE statement in MySQL.

In Rails 2.x:

Table.find(:all, :conditions => ['keywords LIKE ?', '%crescent%'])

In Rails 3.x:

Table.where('keywords LIKE ?', '%crescent%').all

ActiveRecord's Select & Pluck - Andrew Livingston, Yeah, just use a LIKE statement in MySQL. In Rails 2.x: Table.find(:all, :conditions => ['keywords LIKE ?', '%crescent%']). In Rails 3.x: Table.where('keywords  75 Rails 3 + activerecord, the best way to “mass update” a single field for all the records that meet a condition Feb 6 '11

The Postgres database syntax would be:

YourModelName.where("yourFieldName like ?", "%" + yourSearchTerm + "%")

Active Record Query Interface, To return the values from only the question_text column, you would use select or pluck . a SQL statement that offloads the heavy lifting to SQL, which is faster than Ruby for this I've used pluck to scope ActiveRecord model objects to determine which value) , value can either be a single object (like integer , string , etc.)  NOTE: The returned records are in the same order as the ids you provide. If you want the results to be sorted by database, you can use ActiveRecord::QueryMethods#where method and provide an explicit ActiveRecord::QueryMethods#order option. But ActiveRecord::QueryMethods#where method doesn't raise ActiveRecord::RecordNotFound. Find with lock

It all depends on your DB. Is it Postgres? MySQL? MongoDB? Anything else?

With Postgres, you could use something like :

Rails 2.x => Model.find(:all, :conditions=>["models.keywords ~= ?", 'crescent'])
Rails 3.x => Model.where("models.keywords ~= ?", 'crescent')

You just have to find the right syntax for your DB / Rails / ActiveRecord version.

How to Use The Rails Where Method (With Examples), After reading this guide, you will know: How to find records using a variety of methods and conditions. How to specify the order,  You can also create a filter that can be saved into a search, or pinned and re-used across different Kibana apps: image.png 1151×764 53.7 KB It's recommended to use trailing wildcards only for searching for substrings.

I had a similar issue. I needed to see if there are keywords passed from conrolled input in the frontend component in the body of any questions in my questions table. Here is how I did it in my controller:

   def search
     input = params[:q]
       @questions = Question.all
       search_words = input.split(' ')
       @found_question = [];

       search_words.each do |word|
         @found_question << Question.where("questions.body LIKE ?", "%#{word}%")
     end
   end

Ruby on Rails Quick Start, Rails where method helps you query your database to find specific records. By using "where" you can filter records,use multiple conditions, OR, NOT, Get My Ultimate Ruby CheatSheet: This handy 7-page PDF helps you QUICKLY find the Ruby syntax you need, that Ruby method you're Then you'll have to use a string. FIND_IN_SET is useful when you are using explode OR implode to store multiple value. If data stored in database with comma separated value like you have cat_id and you stored it as (1,2,3,5 ) within one field , you can use FIND_IN_SET in select query to match particular value in whole field set.

How to Write Fast Code in Ruby on Rails – Shopify Engineering, This quick start guide is designed to get the Rails developer up and If you're using Timescale Cloud, you can find your credentials in the 'Overview' tab: rails generate scaffold PageLoads user_agent:string time:timestamp In our case we won't be making use of the id column (time-series data is  Method substring() returns a new string that is a substring of given string. Java String substring() method is used to get the substring of a given string based on the passed indexes. There are two variants of this method. In this guide, we will see how to use this method with the help of examples.

Documentation: 9.0: Pattern Matching, Query cache is stored in memory, so its persistence is short-lived. The cache can be disabled, so if your code will run both inside and outside of a  substring is your metadata_0 field from the current item. starting location is 0. end is the output of the first step (index1) output: substring1. Now log substring1. In my case this results exactly in all characters in front of the pipe character:) improve this answer. answered Mar 19 '13 at 8:19. I'm unable to find the action "Find substring

How To Use Array Methods in Ruby, Aside from the basic "does this string match this pattern? can be executed very quickly, regular expressions can be contrived that take arbitrary amounts of You can avoid this by selecting a different escape character with ESCAPE; then a​  I am not asking about the data type. What you actually store in it? Lets say a job runs for 10 minutes. I can populate this column as 600 seconds, 10 min, 0.167 hours and so on.

Comments
  • Isn't this vulnerable to sql injection?
  • No, Rails auto-escapes the parameter. where("keyword LIKE '#{query}'") is vulnerable, but where("keyword LIKE ?", query) is not.
  • Rails 4 works the same way as Rails 3 w/ regards to this question.
  • Awesome! I'm sure I will come back to this answer if the Syntax happens to escape me again. Thanks a bunch.
  • Wondering how expensive this query is? I ran it on a large database, and it froze.
  • Thank you that was also helpful.