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

user1971506 picture user1971506 · Mar 29, 2013 · Viewed 13.4k times · Source

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.

Answer

pablomarti picture pablomarti · Mar 29, 2013

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.