Searching in where in rails with or clause for [IN]

Question

I am using this query in rails

here: @album_ids , @country_ids are Arrays

@audios= Audio.where({:album_id => @album_ids, :country_id => @country_ids})
It produces following SQL:

 Audio Load (0.3ms)  SELECT `audios`.* FROM `audios` WHERE `audios`.`album_id` IN (1, 2) AND `audios`.`country_id` IN (1, 2)

But I want the query to be produced as:

 Audio Load (0.3ms)  SELECT `audios`.* FROM `audios` WHERE `audios`.`album_id` IN (1, 2) OR `audios`.`country_id` IN (1, 2)

OR Rather than AND

Thanks in advance

score 7 · Accepted Answer · answered Aug 16 '12 at 07:49

For Rails 3 (using AREL),

at = Audio.arel_table
audios = Audio.where(at[:album_id].in(@album_ids).or(at[:country_id].in(@country_ids)))

# Audio Load (0.3ms)  SELECT `audios`.* FROM `audios` WHERE ((`audios`.`album_id` IN (1, 2) OR `audios`.`country_id` IN (1, 2)))

score 0 · Answer 2 · answered Aug 16 '12 at 07:39

0

Where clauses will always create an AND, either change it to a 2 liner:

@audios  = Audio.where({:album_id => @album_ids})
@audios += Audio.where({:country_id => @country_ids})

since rails3 doesn't call the searches inline, but instead compiles the query it should do it as one query.

OR:

@audios= Audio.where(["album_id IN (?) OR country_id IN (?)", @album_ids, @country_ids])

answered Aug 16 '12 at 07:39

TomDunning

4,829
1
26
33

Thanks it is working perfectly. But I want the output in only query. Thanks for the response again. – vidur punj Aug 16 '12 at 08:04
if you wanted an actual MySQL query why didn't you just change it yourself or write it in sequel to being with? – TomDunning Aug 16 '12 at 11:23

score 0 · Answer 3 · answered Aug 16 '12 at 07:43

0

Try this method

a = Report.scoped
a.where(
  a.table[:id].in([1,2,3])
    .or( 
      a.table[:model_id].in([3,4,5])
    )
  ).to_sql
=> "SELECT `reports`.* FROM `reports`  WHERE ((`reports`.`id` IN (1, 2, 3) OR `reports`.`model_id` IN (3, 4, 5)))"

answered Aug 16 '12 at 07:43

antiqe

1,125
8
17

I am using this: a = Audio.scoped @audios=a.where(a.table[:album_id].in(@album_ids).or(a.table[:country_id].in(@country_ids))).to_sql ,but not abel to get desired output – vidur punj Aug 16 '12 at 08:01

Searching in where in rails with or clause for [IN]

3 Answers3