Rails: How to set up an IF condition with a JOIN in a has_many :through relationship
I have an application where users can customize a calendar and fill it with a given pool of events. A user can also overwrite a title for his own calendar by an alias. So I have the following has_many :through relation:
class Calendar < ActiveRecord::Base has_many :event_aliases has_many :events, :through => :event_aliases end class Event < ActiveRecord::Base attr_accessible :title has_many :event_aliases has_many :calendars, :through => :event_aliases end class EventAliases < ActiveRecord::Base attr_accessible :course_id, :calendar_id, :custom_name belongs_to :event belongs_to :calendar end
No I want to deliver the calendar with the aliases. If an event has an alias (custom_name), it should be displayed. Otherwise the default event name (title) should be displayed.
Is there a way to easily set up a query that returns all events for the current calendar whether with a custom_name (if exists) or with the default title?
My current solution is to hardcode an if condition into the query which I would like to avoid.
title_column = "case when custom_name IS NOT NULL then custom_name else title end as title" # assume we are given a calendar_id Calendar.find(calendar_id).event_aliases.joins(:event).select(title_column, :event_id).each do |event_alias| # do further stuff here end
I also could fetch all event_aliases and run through each of them to get the default title if necessary.
# assume we are given a calendar_id Calendar.find(calendar_id).event_aliases.each do |event_alias| title = event_alias.custom_name if title.nil? title = Event.find(event_alias.event_id).title # do further stuff here end
But this one results in too many queries to me.
So is there any smarter way of accomplishing what I want? Maybe using named scopes or another fancy rails technique?
I ended up with making a "custom" select via the has_many :through relationship. So the only thing changes is the Calendar model:
class Calendar < ActiveRecord::Base has_many :event_aliases has_many :events, :through => :event_aliases, :select => "event_aliases.custom_name as custom_name, events.*" end
So accessing the custom_name / the title now happens a little like @Doon suggested:
Calendar.find(1).courses.each do |course| title = course.custom_name || course.title end
This creates only 2 queries instead of 3:
Calendar Load (0.6ms) SELECT `calendars`.* FROM `calendars` WHERE `calendars`.`id` = 1 LIMIT 1 Event Load (0.7ms) SELECT event_aliases.custom_name as custom_name, events.* FROM `events` INNER JOIN `event_aliases` ON `events`.`id` = `event_aliases`.`event_id` WHERE `event_aliases`.`calendar_id` = 1
what about using includes to grab the events at the same time as you pull the aliases.
Calendar.find(1).event_aliases.includes(:event).each do |e| puts e.custom_name.blank? ? e.event.title : e.custom_name end
the SQL Rails generates will look something like this:
Calendar Load (0.2ms) SELECT "calendars".* FROM "calendars" WHERE "calendars"."id" = ? LIMIT 1 EventAlias Load (0.2ms) SELECT "event_aliases".* FROM "event_aliases" WHERE "event_aliases"."calendar_id" = 1 Event Load (0.2ms) SELECT "events".* FROM "events" WHERE "events"."id" IN (1, 2)
also if you want to clean it up a bit you can add a virtual field to the EventAlias
class EventAlias < ActiveRecord::Base def name custom_name || self.event.title end end
As long as you use the includes, the queries will be be the same.