Can you tell me about a decent ruby gem that I can use for complex joins?

Question

Piggy backing off another question I posted, I have a complex find() that changes whether or not a certain id is nil or not. See here:

if self.id.nil?
  blocks = AppointmentBlock.find(:first,
    :conditions => ['appointment_blocks.employee_id = ? and ' +
                    '(time_slots.start_at between ? and ? or time_slots.end_at between ? and ?)',
    self.employee_id, self.time_slot.start_at, self.time_slot.end_at,
    self.time_slot.start_at, self.time_slot.end_at],
    :joins => 'join time_slots on time_slots.time_slot_role_id = appointment_blocks.id')
else
  blocks = AppointmentBlock.find(:first,
    :conditions => ['appointment_blocks.id != ? and ' +
                    'appointment_blocks.employee_id = ? and ' +
                    '(time_slots.start_at between ? and ? or time_slots.end_at between ? and ?)',
    self.id, self.employee_id, self.time_slot.start_at, self.time_slot.end_at,
    self.time_slot.start_at, self.time_slot.end_at],
    :joins => 'join time_slots on time_slots.time_slot_role_id = appointment_blocks.id')
end

I'm wondering if there is a gem out there that lets me pass in :first and :conditions type stuff as a block of code. I saw ez_where on github but wasn't sure if it was abandoned or not since its had no activity lately (although that could mean its very solid with no bugs) Any ideas?

Answer 1

When I have a complicated sql query involving a lot of joins, UNION, etc, I create a view in mysql. Then I create a ActiveRecord model and am able to directly read the results via ActiveRecord.

Another advantage of this technique is that the complicated SQL and its execution plan will be calculated only once by the dbms (when you create the view) rather than on each execution.

Depending on the situation, I use either rake or a migration to create/re-create the views.

Note that you'll usually want to re-create the views if any of the underlying tables' schemas are changed.

Added:

Example

class SessionViews2 < ActiveRecord::Migration
  def self.up
    execute "DROP View if exists room_usages"   # Dropping both view and table
    execute "DROP Table if exists room_usages"  # has helped in some corner cases
    execute "CREATE VIEW room_usages AS
 SELECT
    CONCAT(rooms.id,schedules.id) as id 
  , rooms.id             as room_id            
  , rooms.conference_id  as conference_id 
  , rooms.popular_room   as room_popular_room  
  , schedules.id                   as schedule_id
  , schedules.timetable_id         as schedule_timetable_id                  
  , schedules.display_start_time   as schedule_display_start_time    
  , schedules.display_end_time     as schedule_display_end_time       
  , sess.id                as session_id           
  , sess.title             as title            
  , sess.subtitle          as subtitle         
  FROM 
    rooms
    INNER JOIN schedule_rooms ON rooms.id = schedule_rooms.room_id
    INNER JOIN schedules ON schedule_rooms.schedule_id = schedules.id
    INNER JOIN sessions as sess ON schedules.session_id = sess.id
  WHERE
    schedule_rooms.cancelled_time IS NULL 
    AND schedules.cancelled_time IS NULL"  
  end

  def self.down
    execute "DROP View if exists room_usages"
    execute "DROP Table if exists room_usages"
  end
end

Model room_usage.rb

class RoomUsage < ActiveRecord::Base

  ############################################################
  #                                                          #
  #        This is a VIEW! No DB table, Read-Only            #
  #                                                          #
  ############################################################

  belongs_to :conference
  belongs_to :timetable, :foreign_key => :schedule_timetable_id
  belongs_to :session

end

Answer 2

You can also have a look at Arel which:

Arel is a Relational Algebra for Ruby. It 1) simplifies the generation complex of SQL queries

The approach is built into Rails 3 as well. Provides really elegant support for building complex chains of scopes and queries.