1

I'm using Rails 3 with a MySQL database, and I need to programmatically create a query like this:

select * from table where category_name like '%category_name_1%' 
OR category_name like '%category_name_2%'
(...snip...)
OR category_name like '%category_name_n%'

Given the table size and the project scope (500 rows at most, I think), I feel that using something like thinking sphinx would be overkill. I know I could simply do this by writing the query string directly, but wanted to know if there's an ActiveRecord way to do this. There's no mention of this on the official guide, and I've been googling for a long while now, just to end empty-handed :(

Also, is there a reason (maybe a Rails reason?) to not to include the OR clause?

Thanks!

fl00r
  • 82,987
  • 33
  • 217
  • 237
Ramses
  • 996
  • 4
  • 12
  • 28
  • I'm not sure what you mean there is no mention in the guide you sited. Section 2.2 contains a very similar example (with 'and' instead of 'or' and '=' instead of 'like'). – Mark Fraser Mar 30 '12 at 20:20
  • That's right, but please take a look at my reply to your comment below... – Ramses Mar 30 '12 at 21:18

4 Answers4

7

Assuming you have an array names with category names:

Model.where( names.map{"category_name LIKE ?"}.join(" OR "),
  *names.map{|n| "%#{n}%" } )
Harish Shetty
  • 64,083
  • 21
  • 152
  • 198
  • This worked like a charm. just a little modification to your example: `Model.where(names.map{"category_name LIKE ?"}.join(" OR "), *names.map{|n|"%#{n}%"})`... the #{ } around "n" was missing – Ramses Mar 30 '12 at 21:32
4

you should google first, there is already an answer. Look here and then here and you'll get something like this:

accounts = Account.arel_table
Account.where(accounts[:name].matches("%#{user_name}%").or(accounts[:name].matches("%#{user_name2}%")))
Community
  • 1
  • 1
Pavel S
  • 1,543
  • 8
  • 14
1

If you look at the guide, they have examples that can easily be modified to this:

Client.where("orders_count = ? OR locked = ?", params[:orders], false)
Waynn Lue
  • 11,344
  • 8
  • 51
  • 76
  • What if you want to add another OR clause to the query after certain condition? For example, in addition to orders_count and locked, you want to add something like `OR id_number = ?` – Ramses Mar 30 '12 at 21:21
  • Client.where("orders_count = ? OR locked = ? OR id_number = ?", params[:orders], false, id) (?????) – Mark Fraser Mar 30 '12 at 21:30
  • What I meant was how to programmatically add the third (or fourth) OR , not just altering the query string... – Ramses Mar 30 '12 at 21:40
  • 1
    Ah, in that case you would want to use `.or` – Waynn Lue Mar 30 '12 at 22:58
1

Mysql has a regexp function now that can clean things up a bit, assuming there's no regex metachars in your category names:

Table.where "category_name regexp '#{names.join('|')}'"
pguardiario
  • 53,827
  • 19
  • 119
  • 159