Convert a SQL Query to Rails

Question

At the moment, I am doing my complex queries by hand so to speak. But I keep encountering problems. For instance.

          query = "SELECT histories.candidate_id 
          FROM histories 
          WHERE histories.institution_id IN (?) 
          GROUP BY histories.candidate_id 
          HAVING COUNT(*)= ?"
          cand = [Code.find_by_sql([query,

params['searches'][key], params['searches'][key].size])]

class History < ActiveRecord::Base
  belongs_to :candidate
end
  create_table "histories", :force => true do |t|
    t.string   "job_title"
    t.date     "start_date"
    t.date     "finish_date"
    t.string   "basic_salary"
    t.string   "bonus"
    t.integer  "institution_id"
    t.integer  "candidate_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end
class Candidate < ActiveRecord::Base
  # has_and_belongs_to_many :codes
  has_many :codes, :through => :CandidatesCodes
  has_many :histories
  has_many :contacts
  has_many :compensations
end

This returns a list of candidate ids.. but want I want it to return is a list of candidates how would I do that the rails way?

This is brians suggestion, and I have tried this but I get uninitialized constant History::Candidates

      cand = History.find(:all,
        :joins => :candidates,
        :select => "candidates.*",
        :conditions => [ "institution_id IN (?)", params['searches'][key] ],
        :group => [ "candidate_id HAVING count(*) = ?", params['searches'][key].size ]
      )

Answer 1

Try this out.

Assuming that you have mode of History

History.find(:all ,
    :conditions=>[" institution_id IN (? ) ",params['searches'][key] ],
    :group => ["candidate_id  HAVING count(*) = ? " ,params['searches'][key].size ]
)

Answer 2

Give this a try (building on Dinesh's approach above:

candidates = History.find(:all,
  :joins => :candidates,
  :select => "candidates.*"
  :conditions => ["institution_id IN (?)", params['searches'][key]],
  :group => ["candidate_id HAVING count(*) = ?", params['searches'][key].size]
)

Warning - untested.

Answer 3

Try this:

Candidate.all(
  :joins => :histories,
  :conditions => {:histories=> {:institution_id => params[:searches][key]}},
  :group => "candidates.id",
  :having => "count(candidates.id) >= %i" % params[:searches][key].size
)

This should generate the following SQL:

SELECT   candidates.*
FROM     candidates AS candidates
JOIN     histories AS histories ON histories.candidate_id = candidates.id
WHERE    histories.institution_id IN (1,2,3)
GROUP BY candidates.id
HAVING   COUNT(candidates.id) >= 3