Geek asks and answers

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

I am completely new to Rails and have a marginal experience with languages ​​like SQL.

I am working on a Rails 3 project using MySQL2

I have a generic SQL statement that I want to work in BOTH of our databases. Is there a way to do this using only ActiveRecord functions?

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'm trying to execute the WHERE IN statement (and, to be honest, I don’t even know what the WHERE IN statement does, it just does what I need: how do I (or I can) SELECT DISTINCT on multiple columns (postgresql )?) To be more specific, I need to increase the following ActiveRecord function so that it executes the above request:

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

Thank.

+5
source share
2

, , , :

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

, , mY_records, org_id 1,2 3.

: http://guides.rubyonrails.org/active_record_querying.html

: , .

+27

:

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")
0

More articles:


All Articles