Problems with delete records in a Join table

Question

class Program < ActiveRecord::Base
 belongs_to :channel
end


class ProgramSchedule < ActiveRecord::Base
  belongs_to :program
end

Schema.rb

create_table "channels", :force => true do |t|
    t.string   "site"
    t.string   "name"
    t.string   "icon"
    t.string   "type"
    t.string   "country"
    t.integer  "ordertab"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.boolean  "homepage"
  end

  add_index "channels", ["site"], :name => "index_channels_on_site", :unique => true

  create_table "program_schedules", :force => true do |t|
    t.integer  "program_id"
    t.datetime "start"
    t.datetime "stop"
  end

  add_index "program_schedules", ["program_id"], :name => "index_program_schedules_on_program_id"

  create_table "programs", :force => true do |t|
    t.integer  "channel_id"
    t.string   "title"
    t.string   "subtitle"
    t.text     "description"
    t.float    "official_rating"
    t.float    "rating"
    t.string   "type"
    t.integer  "likes"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "programs", ["channel_id"], :name => "index_programs_on_channel_id"
  add_index "programs", ["title", "channel_id"], :name => "index_programs_on_title_and_channel_id", :unique => true

I have to run this SQL Query:

  DELETE program_schedules.*
 FROM program_schedules INNER JOIN programs ON program_schedules.pid = programs.id
                        WHERE programs.channel_id = '1'
                        AND (
                               program_schedules.start >= '2011-12-12'
                                AND program_schedules.start <= '2011-12-12'
                           )

in Ruby on Rails...

ProgramSchedule.joins(:program).delete_all(
    :start => (dates_array.min)..(dates_array.max),
    :programs => {:channel_id => 1}
    )

Error: (ActiveRecord::StatementInvalid) /Library/Ruby/Gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:283:in `query':

     Mysql2::Error: Unknown column 'programs.channel_id' in 'where clause':
     DELETE FROM `program_schedules` WHERE `programs`.`channel_id` = 1 AND (`program_schedules`.`start` BETWEEN '2011-12-09 23:00:00' AND
 '2011-12-10 22:20:00') 

Answer 1

Is your table actually called programs (with a trailing s) or program (without). Because in your handwritten query you're referencing the table without the 's' and in your Rails query with...

Answer 2

The generated SQL doesn't appear to actually be executing the join. Try restructuring the query like so :

ProgramSchedule.joins(:program)
               .where(start: (dates_array.min) ..(dates_array.max)
               .where(programs: {channel_id: 1})
               .delete_all

You don't have to use 2 where clauses, I just do it to keep my join conditions separate from the conditions on the original table.