PG::Error: ERROR: operator does not exist: integer ~~ unknown

Question

I'm making a search-function in a Rails project with Postgres as db.

Here's my code

def self.search(search)
   if search 
    find(:all, :conditions => ["LOWER(name) LIKE LOWER(?) OR LOWER(city) LIKE LOWER(?) OR LOWER(address) LIKE LOWER(?) OR (venue_type) LIKE (?)", "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
  end

But my problem is that "venue_type" is an integer. I've made a case switch for venue_type

def venue_type_check
    case self.venue_type
      when 1
        "Pub"
      when 2
        "Nattklubb"
    end
end

Now to my question: How can I find something in my query when venue_type is an int?

Please try with: find(:all, :conditions => ["LOWER(name) LIKE LOWER(?) OR LOWER(city) LIKE LOWER(?) OR LOWER(address) LIKE LOWER(?) OR (venue_type = ?)", "%#{search}%", "%#{search}%", "%#{search}%", search.to_i]) — Bachan Smruty, Jul 01 '13 at 10:01
Where is the error `PG::Error: ERROR: operator does not exist: integer ~~ unknown` being thrown? — zeantsoi, Jul 01 '13 at 10:17
@BachanSmruty I tried your way, but when I search for "Nattklubb" (venue_type), I don't get any results. :/ — maverick, Jul 01 '13 at 11:45

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

I think you have to use if-else condition in your where clause something like following

find(:all, :conditions => ["LOWER(name) LIKE LOWER(?) OR LOWER(city) LIKE LOWER(?)
            OR LOWER(address) LIKE LOWER(?) OR    
    IF(venue_type == 1, 'Pub', IF(venue_type == 2, 'Nattklubb2',  '')) LIKE (?)", 
                 "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%"])

edited May 23 '17 at 11:50

Community

1
1

answered Jul 01 '13 at 10:19

Salil

46,566
21
122
156

devanand · Answer 2 · 2013-12-19T10:36:26.327

0

If you are using rails 3.2 or greater, you should use where( [] ) instead for find.

where( [ "field like ?", field ] ).order(). ...

Also, when using pgsql why are you not using ilike instead of like with lower?

In your case while searching int or not int, I would use different queries.

When search.to_i != 0

edited Dec 19 '13 at 10:36

answered Jul 01 '13 at 10:36

devanand

5,116
2
20
19

Bachan Smruty · Accepted Answer · 2013-07-02T09:51:45.530

0

Please try this code. Here I have added switch case to where clause.

def self.search(search)
           if search 
            find(:all, :conditions => ["LOWER(name) LIKE LOWER(?) OR LOWER(city) LIKE LOWER(?) OR LOWER(address) LIKE LOWER(?) OR (venue_type = CASE WHEN 'pub' = ? THEN 1 WHEN 'nattklubb' = ? THEN 2 END)", "%#{search}%", "%#{search}%", "%#{search}%", "#{search.downcase}", "#{search.downcase}"])
            else
              find(:all)
            end
end

edited Jul 02 '13 at 09:51

answered Jul 01 '13 at 13:18

Bachan Smruty

5,686
1
18
23

Yes, I tried your code, but for some reason I get this error: PG::Error: ERROR: syntax error at or near ")" LINE 1: ...ub' = '%white%' THEN 1 WHEN 'Nattklubb' = '%white%' THEN 2)) ^ : SELECT "venues".* FROM "venues" WHERE (LOWER(name) LIKE LOWER('%white%') OR LOWER(city) LIKE LOWER('%white%') OR LOWER(address) LIKE LOWER('%white%') OR (venue_type = CASE WHEN 'Pub' = '%white%' THEN 1 WHEN 'Nattklubb' = '%white%' THEN 2)) – maverick Jul 02 '13 at 08:57
@rsvmrk, sorry, I missed the END in the switch case. I have modified it. Please check it now – Bachan Smruty Jul 02 '13 at 09:04
I don't know if my comment made any sense now, sorry for that. – maverick Jul 02 '13 at 09:05
No no.. thanks for your comment otherwise how could I know that, there is some error in my answer.. thanks for the comment.. :) – Bachan Smruty Jul 02 '13 at 09:07
Oh, thanks, it works out kind of... The funny part is: when I search for 1 or 2, then "Nattklubb" or "Pub" appears. How can I reverse this? – maverick Jul 02 '13 at 09:11
I have modified the query, have a try with that. – Bachan Smruty Jul 02 '13 at 09:52

PG::Error: ERROR: operator does not exist: integer ~~ unknown

3 Answers3