Complex SQL in Rails

How do I form the following query using active record?

SELECT c.* 
FROM `course_enrollments` ce JOIN courses c ON ce.course_id = c.id 
开发者_如何学PythonWHERE ce.created_at
BETWEEN '2000-01-01' and '2012-01-01' [AND ANOTHER POSSIBLE CONDITION]
GROUP BY c.id

I want to be able to do something like: (I know the below is not correct, but I just want to show a general example)

courses = Course.joins(:course_enrollments).where('course_enrollments.created_at' => params[:start_date]..params[:end_date]).group('courses.id')

if some_condition
  courses = courses.where(:some_field => 1)
end

The following should get you on the way

Course.joins(:course_enrolements).
where("course_enrolements.created_at between '2000-01-01' and '2012-01-01'").
group("courses.id").
where(MORE CONDITIONS)

use .to_sql to analyze output

Take a look at this Railscast. There are quite a number of ways to do the same elegantly esp. to address your [AND ANOTHER POSSIBLE CONDITION] concern. Also take a look at Squeel gem if its not suggested in the Railscast.