1

Hello I struggle creating a specific query.

I have a searchfield with input parameter "{search}". I want to use this search field to find all tables which have a plate with veggies of a specific flavor or description.

I already got a tricky query which does exactly what it is supposed to do - so this works perfectly fine!

Tables.where(id: Plate.select("plate_id").where(Veggie.select("id").where('description LIKE ? OR flavor LIKE ?', "%#{search}%","%#{search}%")))

Aside from Veggies, suppose there is also a SQL-Table calles Fruits. Now i want to search for all Tables which have Plates which have Veggies OR(!!!) Fruits fitting to the description or flavor of the searchfield input.

I experimented a lot but can not find the right way. Here is one of my experiments which does not work though:

Tables.where(id: Plate.select("plate_id").where((Veggie.select("id").where('description LIKE ? OR flavor LIKE ?', "%#{search}%","%#{search}%")).or(Fruit.select("id").where('color LIKE ? OR flavor LIKE ?', "%#{search}%","%#{search}%")))

The code above does not work! The problem is, I can not figure out how to combine those 2 subqueries. The or statement does not seem to work. Any help appreciated.

EDIT: As has been pointed out, i now tried doing it with the UNION command. Unfortunately the .union() option with the Gem active_record_union seems to work only for rails 4+.

So i simulated a union with the following code.

def self.search2(search2)
 if search2
  if search2.length > 0
    @Plate1 = Plate.where(veggie_id: Veggie.select("id").where('description LIKE ? OR color LIKE ?', "%#{search2}%","%#{search2}%"))
    @Plate2 = Plate.where(fruit_id: Fruit.select("id").where('description LIKE ? OR color LIKE ? OR flavor LIKE ?', "%#{search2}%","%#{search2}%", "%#{search2}%" ))
    @Plate12 = @Plate1+@Plate2
    @table_ids = Array.new
    @Plate12.each do |plate|
    @table_ids.push(plate.table_id)
   end
   where(id: @table_ids)
  else
   scoped
  end
 else
  scoped
 end
end

Actually, i do believe this is very messy! It DOES work as intended, but i am a fraid it is a bit slow and unperformant. If anyone has a better suggestion i am glad about any help.

Donselm
  • 77
  • 1
  • 8
  • 2
    possible duplicate of [Hash notation for ActiveRecord OR query](http://stackoverflow.com/questions/31096009/hash-notation-for-activerecord-or-query) – potashin Jul 03 '15 at 13:07
  • 1
    Title may confuse you, but take a look at the second option, the one with `where_values`, it should help you. – potashin Jul 03 '15 at 13:08
  • Thanks, that is indeed helpful. I still have problems to understand how to replace the "values" with my subqueries. I just can not get it to work. – Donselm Jul 03 '15 at 16:26
  • You don't want an 'or' there,, you need to do a SQL UNION to combine the veggie ids and the fruit ids – ilan berci Jul 03 '15 at 17:29
  • I think that got me to the right track. Unfortunately i use rails 3.2 so i couldnt use the active_record_union Gem. So i had to figure out how to do a UNION. I have a solution now - not really happy because i think it is very unperformant. I edit it up into my inital post. If anyone has a better or fast suggestion - i d be more than happy. – Donselm Jul 04 '15 at 00:21

0 Answers0