Rails: How to set up an IF condition with a JOIN in a has_many :through relationship

Question

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?

UPDATE

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

Doon · Answer 1 · 2012-07-17T13:24:01.460

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.

Thanks for the hint. Well, the `includes()` solution isn't so much different from the `joins()` solution. In your solution you just make the if-condition in Ruby instead of SQL. I came up with another solution that is influenced by this, but needs one query less. — LeEnno, Jul 18 '12 at 06:52

Rails: How to set up an IF condition with a JOIN in a has_many :through relationship

1 Answers1