Query multiple tables and columns in Rails and Postgresql with a single search parameter

Question

I'm constructing an API using Rails with a Postgresql database. I have a User model and the users table has 3 columns, email, name and city.

I would like to create a single URL that allows users to search multiple fields in my User model, so for example, when the user searches in the following URL:

http://localhost:3000/search?term=foo

it searches for the term 'foo' in the email, name and city columns and return all the matches. This is similar to when you search on the iTunes Store (http://itunes.apple.com/search?term=foo).

So far this is the query I have:

SELECT 
  email
  name
  city
FROM
  users
WHERE
  email = '#{term}' OR
  name = '#{term}' OR
  city '#{term}';

This works but is way too slow (>4000ms) when you have a lot of records. Does anyone know a better SQL or a Rails/ActiveRecord method that can make this query better? I already added indexes to all three columns.

Thanks a lot!

jyrkim · Accepted Answer · 2014-10-22 21:29:38Z

1

If you're planning to use that code for production purposes, then it's recommended to use ? marks in the query string, in order to avoid SQL Injection. Like in the following sample:

User.where("email = ? OR name = ? OR city = ?",  params[:term],  params[:term], params[:term])

You asked how things could be made speedier, well database indexes might work in this case; so you need to create migration file that could be called AddIndexes

class AddIndexes < ActiveRecord::Migration
  def change
     add_index :users, :email
     add_index :users, :name
     add_index :users, :city
  end
end

answered Oct 22, 2014 at 21:29

jyrkim

2,8791 gold badge26 silver badges35 bronze badges

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

4 Comments

Harry Wang Over a year ago

Thanks a lot for the suggestion on avoiding SQL injection. As for the adding index part, I've already added indexes to the 3 columns, but it's still slow (>4000ms) for some queries. I have about 3 million records in this table, I guess that may just be too big.

jyrkim Over a year ago

@HarryWang How about using batches? I just had a look at api.rubyonrails.org/classes/ActiveRecord/Batches.html (a new concept for me too!) and guides.rubyonrails.org/… You might find this Rails cast useful too railscasts.com/episodes/202-active-record-queries-in-rails-3 So when using batches you could limit the amount of Users used in the SQL condition with id’s or with batch_size. Perhaps this could speed up things.

jyrkim Over a year ago

@HarryWang about the SQL, you could also ask from Database Administrators' stack exchange (dba.stackexchange.com), about how to make the SQL quicker too

Harry Wang Over a year ago

Thanks for noting batches. This is very helpful! Thanks a lot!!

Collectives™ on Stack Overflow

Query multiple tables and columns in Rails and Postgresql with a single search parameter

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related