Rails basic search and PostgreSQL

Question

I'm trying to create basic search in my web app. Here's code of search function.

def self.search(title, category_id, city_id)
    if title || category_id || city_id
      joins(:category).where('title LIKE (?) AND category.category_id IN (?) AND city.city_id IN (?)', "%#{title}%", "%#{category_id}%", "%#{city_id}%")
    else
      scoped
    end
  end

I have these associations in my model:

  has_one :category
  has_one :city

And I get this error

ActionView::Template::Error (PG::Error: ERROR:  missing FROM-clause entry for ta
ble "category"
LINE 1: ..._id" = "events"."id" WHERE (title LIKE ('%%') AND category.c...

I'm using PostgreSQL. What I can do to remove this error?

mu is too short · Accepted Answer · 2012-03-11 00:24:21Z

7

The form of joins that you're using wants the association name, the SQL wants the table name. The table should be called categories.

A few other things:

I don't see you joining :city anywhere so your next error will be "Missing FROM-clause entry for table "city". The solution will be to .joins(:city) and use cities in the where. But keep reading anyway.
You don't need the parentheses around the value for LIKE, just title LIKE ? is fine.
You're using IN expressions for the category and city but you're giving them LIKE patterns and that won't work: the IDs will be numbers and you can use LIKE with numbers. If you're using IN then you'll usually want to supply a list of possible values, if you only want to match one value then just use = and a single value for the placeholder.
The categories table probably doesn't have a category_id column, similarly for the cities table and city_id column. Those two columns should be in your model's table.
Searching for a title when you don't have a title doesn't make much sense. Similarly for country and city.

That looks like a lot of problems but they can be fixed without too much effort:

def self.search(title, category_id, city_id)
    rel = scoped
    rel = rel.where('title    like ?', "%#{title}%") if(title)
    rel = rel.where('category_id = ?', category_id)  if(category_id)
    rel = rel.where('city_id     = ?', city_id)      if(city_id)
    rel
end

and you don't even need joins or explicit table names at all.

edited Mar 11, 2012 at 0:24

answered Mar 11, 2012 at 0:09

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

Michael Durrant Over a year ago

+100 As usual mu has provided a great elegant solution plus great explanation. Time for mucasts !

mu is too short Over a year ago

@MichaelDurrant: Thanks but I don't have the voice for 'casting.

Collectives™ on Stack Overflow

Rails basic search and PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related