5

I want to union multiple active record relation

For example,

apple_companies = Company.where("name like ?","%apple%")
banana_companies = Company.where("name like ?","%banana%")

I want to combine these two relation.

not merge, merge is apple_companies.merge(banana_companies) => Company.where("name like ? and name like ?", "%apple%","%banana%")

I want to Company.where("name like ? or name like ?", "%apple%","%banana%")

afterward,

I will code

companies = Company.none
company_name_list.each do |name|
    like = "%"+name+"%"
    companies += Company.where("name like ?",like)
end

but code which I wrote make companies to array.....

So I cannot order and page to companies... :(

thank you

user3580287
  • 73
  • 1
  • 1
  • 6

6 Answers6

5
apple_companies = Company.where("name like ?","%apple%")
banana_companies = Company.where("name like ?","%banana%")

apples = apple_companies.where_values.reduce(:and)
bananas = banana_companies.where_values.reduce(:and)

Company.where(apples.or(bananas))

See ActiveRecord Arel OR condition for more examples.

Community
  • 1
  • 1
Jason Noble
  • 3,756
  • 19
  • 21
5

The best result for this that I've come across is to grab and merge the ids of the two queries and then search for them like this:

apple_companies = Company.where("name like ?","%apple%").pluck(:id)
banana_companies = Company.where("name like ?","%banana%").pluck(:id)
ids = apple_companies + banana_companies.uniq
Company.where(id: ids)

It's four lines that seems like it should be doable in one but it works.

Skinny
  • 91
  • 1
  • 6
  • There is no need for the uniq, because the database won't return duplicate rows. But i'm giving this an upvote because this is what i was going to suggest – dabobert May 31 '17 at 15:50
  • 1
    Using `ids` instead of pluck works too: `apple_companies = Company.where("name like ?","%apple%").ids banana_companies = Company.where("name like ?","%banana%").ids` – eXa Aug 10 '17 at 03:53
2

In this case you can use any of the other answers. However in more general cases, I strongly recommend using any_of gem. With this gem you can do:

apple_companies = Company.where("name like ?","%apple%")
banana_companies = Company.where("name like ?","%banana%")

Company.where.any_of(apple_companies, banana_companies)

There already is a pull request to add this functionality to future rails releases.

BroiSatse
  • 44,031
  • 8
  • 61
  • 86
1

Please try using

Company.where('name LIKE ? OR name LIKE ?','%apple%', '%banana%')

OR

Company.where('name IN (?)', ['%apple%', '%banana%'])

According to your code:

names = []
company_name_list.each do |name|
    names << "%"+name+"%"
end

Then you can do: 

companies = Company.where('name LIKE ANY(Array[?])', names)
manu29.d
  • 1,538
  • 1
  • 11
  • 15
  • Company Load (1.4ms) SELECT `companies`.* FROM `companies` WHERE (name LIKE ANY(Array['%코리아%','%한국%'])) Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Array['%코리아%','%한국%']))' There is an error. – user3580287 Apr 29 '14 at 07:59
  • Company.where('name IN (?)', ['%apple%', '%banana%']) This is not "like" – user3580287 Apr 29 '14 at 08:09
  • Company.where('name LIKE ? OR name LIKE ?','%apple%', '%banana%') I can not use that kind of syntax – user3580287 Apr 29 '14 at 08:10
  • This is my mistake. There is no Array datatype in MySql. This syntax is valid in PostrgesSql. – manu29.d Apr 29 '14 at 10:07
0

Late answer, but Arel will solve it.

Company.where(Company.arel_table[:name].matches_any(["%apple%", "%banana%"]))
244an
  • 1,579
  • 11
  • 15
-1

Use

Company.where(name: IN ('%apple%','%banana%'))
Uri Agassi
  • 36,848
  • 14
  • 76
  • 93
Babar
  • 1,202
  • 1
  • 12
  • 21
  • This results in a syntax error. You could use `Company.where(name: ['%apple%','%banana%'])`, but it wouldn't do the `like` syntax the OP requested. – Jason Noble Jun 21 '16 at 22:04