Postgresql Ilike with Multiple Matches in Rails Activerecord

PostgreSQL ilike with multiple matches in Rails ActiveRecord

You can do it by

User.where('name ilike any ( array[?] )',['%thomas%','%james%','%martin%'])

How to use ILIKE for multiple values in single column in rails?

Use:

Project.where("name ILIKE ANY (array[?])", ["%Arvind Oasis%", "%Prestige Jindal City%", "%XXXX%"])

Multiple LIKE and AND operators in RAILS/POSTGRESQL

PostgreSQL has a handy expr op all (array) expression so you can say things like:

where content like all (array['%a%', '%b%', '%c'])

as a short form of:

where content like '%a%'
  and content like '%b%'
  and content like '%c%'

Also, ActiveRecord will conveniently replace a ? placeholder with a comma-delimited list if you hand it a Ruby array. That lets you say things like:

Word.where('content like all (array[?])', %w[a b c].map { |c| "%#{c}%" })

and:

Word.where('content like all (array[?])', some_other_array.map { |c| "%#{c}%" })

Pattern matching in rails ( where column LIKE '%foo%) with Postgres

Use a parameterized query instead to avoid SQL-injections, like so:

Person.where('name LIKE ?', '%' + query + '%')

Note that the percent signs must be part of the parameter, not the where clause or Rails will escape it and you'll get a syntax error. (At least on postgres.)

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "%"
LINE 1: ...name LIKE %'John...
                     ^

Slow wildcard search LIKE across multiple columns PostgreSQL/Rails

Like mu already hinted: create a trigram index on the target column(s). For the first example:

CREATE INDEX tbl_name_gin_trgm_idx  ON tbl USING gin (name gin_trgm_ops);

A trigram index also supports case insensitive matching, but to cooperate with a plain trigram index, rewrite the second example with ILIKE:

WHERE name ILIKE :search OR barcode ILIKE :search OR sku ILIKE :search

You can support this with three indices like the example above. That's most versatile. Like, when various columns might be combined in the same search.

Or one index with three index columns. Less versatile, but faster and smaller.

Or one trigram index with one concatenated expression. Least versatile, but fastest. Using the custom function immutable_concat_ws() as defined and explained here:

• Create an immutable clone of concat_ws

CREATE INDEX tbl_special_gin_trgm_idx ON tbl USING gin (immutable_concat_ws('|', name, barcode, sku) gin_trgm_ops);

Combined with this WHERE clause in queries:

WHERE immutable_concat_ws('|', name, barcode, sku) ILIKE :search
AND  (name ILIKE :search OR barcode ILIKE :search OR sku ILIKE :search)

The first line of the WHERE clause pulls in the fast index.

The second line excludes possible (rare) false positives if :search matches across the separator.

If :search never contains the chosen separator | nor nested wildcards, we can drop the second line.

See:

• PostgreSQL LIKE query performance variations

• LOWER LIKE vs iLIKE

• String concatenation using operator "||" or format() function

• Query performance with concatenation and LIKE

multi-table, mult-filters postgresql search - rails

Take a look at Rails join table queries and Rails method chaining to start.

Let's look at some examples of what you might do. Let say you want to find users who have a profile name of "John" and a subject with the grade of 100. You can do the following:

@users = User.where(profiles: { name: "John" }, subjects: { grade: 100 }).joins(:profiles, :subjects)

Note that using the hash method works only if you filter based on specific values. Let say now you want to find users who have a profile name that beings with "John" (such as "John", "John Smith", or "John Doe") and a grade greater than 85, you would do the following:

@users = User.where("profiles.name ILIKE ? AND subjects.grade > ?", "John%", 85).joins(:profiles, :subjects)

The ILIKE query works with Postgres, not MySQL as far I remember. Note the fact that in both statements you have to mention the joining table name in the query and you have to call the joins method as well.

So now that you know how to join tables, we can now look at how to use the params and scopes to filter it.

class User
  scope :by_profile_name, -> (input) do
    where("profiles.name = ?", input[:profile_name]).joins(:profiles) if input[:profile_name]
  end

  scope :by_subject_grade, -> (input) do
    where("subjects.grade = ?", input[:subject_grade].to_i).joins(:subjects) if input[:subject_grade]
  end
end

Then in your controller you would have:

@users = User.by_subject_grade(params).by_profile_name(params)

This is just a rough start take a look at the links above for more details.

How to search an array column for multiple strings

User.where(names: ['robert', 'bob'])

User.where("names IN (?) ", ['robert', 'bob'])

note: Its case sensitive

This answer will also helps you

Query partial match for tags and another attribute in rails 4

You use :q both in iLIKE and = ANY (tags), maybe this would work better:

where(":q = ANY (tags) OR 'title' iLIKE :iq", q: query, iq: "%#{query}%")

---

Also ANY is not ILIKE, to create the equivalent see here:

For the benefit of future searches I used:

create function reverse_ilike(text, text) RETURNS boolean AS 'select $2 ilike $1;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL

INPUT;

create operator ~~*^ (PROCEDURE = reverse_ilike, LEFTARG = text, RIGHTARG = text);

Best,

Which should (I did not test this) enable you to do:

where(":q ~~*^ ANY (tags) OR 'title' iLIKE :iq", q: query, iq: "%#{query}%")

---

Edit

Also, it seems that you have a typo - you check the input query is iLike the string 'title' instead of the column title - the query should be:

where(":q ~~*^ ANY (tags) OR title iLIKE :iq", q: query, iq: "%#{query}%")

---

Related Topics