ActiveRecord destroy_all throws StatementInvalid

Question

I am using ActiveRecord with oracle adapter on Ruby on Rails. I am getting a StatementInvalid Exception when trying to delete a row.

Here is how my table looks like : room_user_table

room | user
1010 | a
1010 | b
1011 | a
1011 | c
1011 | d

My ruby ActiveRecord class:

class RoomUserTable < ActiveRecord:Base
    self.table_name = 'room_user_table'
end

Now I want to delete the 2nd row for example, so I am issuing

RoomUserTable.destroy_all(:room => 1010, :user => 'b')

But this is throwing ActiveRecord::StatementInvalid Exception

OCIError: ORA-01741: illegal zero-length identifier: DELETE FROM "ROOM_USER_TABLE" WHERE "ROOM_USER_TABLE"."" = :a1

Any help would be much appreciated.

My test_controller.rb

class TestController < ActionController::Base
    def test
       RoomUserTable.destroy_all(:room => 1010, :user => 'b')
    end
end

Answer 1

Your RoomUserTable doesn't have a primary key, which is causing it to run the query that you have in your question WHERE "ROOM_USER_TABLE"."" = ... which in turn is causing Oracle to throw a wobbly. Rails models need to have primary keys.

The table looks like a join table therefore you don't need to create a model for it or query it directly at all. You can use a has_and_belongs_to_many relationship between User and Room and specify the join table.

class Room < ActiveRecord::Base
  has_and_belongs_to_many :users, join_table: :room_user_table
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :rooms, join_table: :room_user_table
end

Or similar.

Edit - having said that you have a, b etc in your user column, so I've no idea what that table is, but the problem is still the same, you don't have a primary key.