0

I would like to make a birthday calendar based on the example in Railscast #213 (Revised) for our members for each month. Each member has a birth_date and has_many memberships at different locations.

To solve the problem, I thought I would make a hash of every members birthday by location, by day and month. Then I could walk through the current month and pull the name of every one with a birthday on that day.

From the member index controller

@members = current_org
  .members.joins(:memberships)
  .where("memberships.location_id = #{params[:location_id]}")
  .find_by_sql("SELECT members.*, EXTRACT(DOY FROM birth_date) as yday FROM children ORDER BY yday, first_name")

And then create hash

@member_birthday = @members.group_by { |m| "#{m.birth_date.day}-#{m.birth_date.month}" }

The first issue I have is that the activerecord query returns every member and ignores the where clause. The second issue is if I return an array, the day method does not work. Thirdly, the only way I could get the order right was to put an EXTRACT DOY inside find_by_sql.

Is there a way to do this in rails 3.1 in activerecord and get rid of the find_by_sql? Is there another fast way to accomplish this?

JHo
  • 1,068
  • 1
  • 14
  • 29

1 Answers1

2

I believe your "find_by_sql" is overriding everything else in the relationship that you are building, instead of calling find_by_sql there, restructure your query like such:

current_org.select("members.*, EXTRACT(DOY FROM birthday_date").joins("membership on memberships.id = X").where(....)

SomeDudeSomewhere
  • 3,928
  • 1
  • 23
  • 27
  • Thanks. I was able to get your suggestion to work by making a few modification: – JHo Sep 05 '13 at 10:13
  • `@members = current_org.members.select("members.*, EXTRACT(DOY FROM birth_date) AS yday").joins(:memberships).where("memberships.location_id = #{params[:location_id}").order("yday")` And it was much faster than my previous query. – JHo Sep 05 '13 at 10:24
  • Also JHo, do note that you are exposing yourself to SQL injection with that structure of the where. it's better to pass the params as an array to where so that Rails is able to cleanse them properly. Like such Model.where("criteria = ?", params[:some_id]). – SomeDudeSomewhere Sep 05 '13 at 14:47