CODEWITHSUNDEEP
ruby-on-railspostgresql
Harry Wang
Harry Wang

Reputation: 1136

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

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!

Upvotes: 1

Views: 1435

Answers (1)

jyrkim
jyrkim

Reputation: 2869

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

Upvotes: 1

Related Questions