ERROR: missing FROM-clause entry for table “self”

When creating a model Deal, I use an after_create to create prizes on the DealPrize table.

Deal and DealPrize have a belong to/has_many relations: a Deal has many Deal prizes and a Dealprize belongs to a Deal.

It works like this: inside Deal, I have a column 'prize-number' and I use an after_create so that evetytime the amdin creates a new deal, the app takes this prize_number column, and create this volume of prizes (inserting as many rows as necessary) inside the DealPrize table.

I fail to use my Rails app moel variable inside a postgresql query.

Here is a working query (using figures instead of variables). It perfetcly writes it on my database.

model deals.rb

CONNEXION = ActiveRecord::Base.connection

    def create_prizes
      Deal.transaction do        
        self.120000.times do |i|
          CONNEXION.execute "INSERT INTO deal_prizes (deal_id, created_at, updated_at) 
          values ( 62, '2009-01-23 20:21:13', '2009-01-23 20:21:13')"
        end
      end
    end

But when I replace the example values by variables as it should be, I get an error.

CONNEXION = ActiveRecord::Base.connection

    def create_prizes
      Deal.transaction do        
        self.120000.times do |i|
          CONNEXION.execute "INSERT INTO deal_prizes (deal_id, created_at, updated_at)
          values ( self.id, '2009-01-23 20:21:13', '2009-01-23 20:21:13')"
        end
      end
    end

If I use self.if, I get error

ERROR: missing FROM-clause entry for table "self"

I tried also self.id, id, deal.id, deal_id, self_id, nothing works.

thanks for your help,

EDIT - ADDED for dimakura

Before optimizing with raw sql and transactions:

def create_prizes
      self.1200000.times do
        prizes = DealPrize.create(:deal_id => self.id, :admin_user_id => self.admin_user_id)
        prizes.save
      end
    end

LATEST CODE with the help of dimakura

CONNEXION = ActiveRecord::Base.connection

def create_prizes
  Deal.transaction do  
    self.1200000.times do |i| 
      st = CONNEXION.raw_connection.prepare("INSERT INTO deal_prizes (deal_id, created_at, updated_at) values (?, ?, ?)")
      st.execute(self.id, Time.now, Time.now)
      st.close
    end
  end
end

Getting error:

wrong number of arguments (1 for 2..3)

回答1:

It should be:

CONNEXION.execute "INSERT INTO deal_prizes (deal_id, created_at, updated_at)
values ( #{self.id}, '2009-01-23 20:21:13', '2009-01-23 20:21:13')"

But it's not a recommended approach. You should never interpolate parameters like this.

The better approach is:

st = CONNEXION.raw_connection.prepare("INSERT INTO deal_prizes (deal_id, created_at, updated_at) values (?, ?, ?)")
st.execute(self.id, Time.now, Time.now)
st.close

回答2:

You have to do it with string interpolation i.e. #{self.id} instead of self.id:

   def create_prizes
      Deal.transaction do        
        self.120000.times do |i|
          CONNEXION.execute "INSERT INTO deal_prizes (deal_id, created_at, updated_at)
          values ( #{self.id}, '2009-01-23 20:21:13', '2009-01-23 20:21:13')"
        end
      end
    end