How to use WHERE IN in Rails 3 project using MySQL 2?

Question

I am quite to new to Rails and have marginal experience with SQL-type languages.

I am working on a Rails 3 project using a MySQL2

I have here a generic SQL statement that I want to work on BOTH of our databases. Is there any way to do this through just the ActiveRecord functionality?

SELECT * FROM MyRecords 
WHERE  (f1, f2, f3, f4) IN (
    SELECT f1, f2, f3, f4
    FROM   MyRecords
    GROUP  BY f1, f2, f3, f4
    HAVING count(*) = 1 
);

In other words, I am trying to execute a "WHERE IN" statement (and to be frank, I don't even know what a WHERE IN statement does, just that this answer does what I need: How do I (or can I) SELECT DISTINCT on multiple columns (postgresql)? ) To be more specific, I have to augment the following ActiveRecord function so that it does the above query:

  def MyRecordFunction
    MyRecords.where('org_id=?', self.org_id).order('f4')
  end

Thank you.

A MySQL IN clause simply is an inclusive OR. EG: WHERE this IN('1','2','3') is the same as doing. WHERE this = '1' OR this = '2' OR this = '3' http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in — Will B., Mar 29 '13 at 17:06

score 27 · Answer 1 · edited Nov 16 '20 at 13:11

I'm a bit confused, but I think this is what you are looking for:

MyRecord.where(org_id: [1, 2, 3]).order('f4')

The in statement inside of where look for specific values for certain field, in the example above, the query will look for register in mY_records table where the org_id contains 1,2 or 3.

Check this out: http://guides.rubyonrails.org/active_record_querying.html

Update: I understand, Harish Shetty has the point.

score 0 · Answer 2 · answered Mar 29 '13 at 17:14

Try this:

MyRecord.joins("JOIN
  (
    SELECT f1, f2, f3, f4
    FROM   my_records
    GROUP  BY f1, f2, f3, f4
    HAVING count(*) = 1 
  ) a ON a.f1 = my_records.f1 AND
         a.f2 = my_records.f2 AND
         a.f3 = my_records.f3 AND
         a.f4 = my_records.f4")

How to use WHERE IN in Rails 3 project using MySQL 2?

2 Answers2