CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3activerecord
David R
David R

Reputation: 328

Trying to get the first record for each day of the week in Rails via SQL if possible

I have a weekly view of projects. For each project I'm grabbing the first record for each day of the week. It's important to me to always get the first record for that day, and know which weekdays have no records. Right now I'm doing this with a separate SQL string for each project + day of week combo in a nested for loop.

This feels wrong. It seems like I should be able to get this with ActiveRecord / SQL via Group By and Limit, and use Ruby/Rails to process the results. Is this possible?

Thanks in advance. – David

Upvotes: 0

Views: 864

Answers (1)

Wawa Loo
Wawa Loo

Reputation: 2276

Does that do the job? (Using MySQL)

@projects = []
for day in 1..7
  @projects.push Project.where("DAYOFWEEK(project_date) = ?", day).where(:project_date => Time.now.all_week).order('project_date desc').first
end
#Time.now.all_week assumes you're working with Rails 3.2

Take a look at DATEOFWEEK() in MySQL docs

You can test with

for pj in @project
  p pj.project_date if pj.project_date #nil if no project found
end

Upvotes: 1

Related Questions