ERROR: missing FROM-clause entry for table "self" (Rails 4 Active Record transaction, postgresql 9.4))

Question

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)

I'm sorry but I've shown you a correct way. Now you have changed your question. You should ask another question if you like. — dimakura, Sep 04 '15 at 20:57
put it inside question http://stackoverflow.com/questions/32406552/wrong-number-of-arguments-1-for-2-3-for-postgressql-query-rails-4-postgresql — Mathieu, Sep 04 '15 at 21:02

dimakura · Accepted Answer · 2015-09-04T20:35:34.817

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

edited Sep 04 '15 at 20:35

answered Sep 04 '15 at 20:22

dimakura

7,575
17
36

for your 'even better' solution, I get "undefined method `prepare' for #" – Mathieu Sep 04 '15 at 20:29
Yes, it should be `CONNEXION.raw_connection.prepare("INSERT INTO deal_prizes (deal_id, created_at, updated_at) values (?, ?, ?)")`. I've updated answer as well. – dimakura Sep 04 '15 at 20:32
Don't you have a `DealPrize` model too? – dimakura Sep 04 '15 at 20:33
Why not to execute simply: `DealPrize.create(deal_id:62)`? – dimakura Sep 04 '15 at 20:34
1

I have a working query but as I need a mass insert (>100K rows), I want to improve it by using raw sql+transaction. I'm puttting now my original pure rails active record query using DealPrize – Mathieu Sep 04 '15 at 20:35
Why not to execute simply: DealPrize.create(deal_id:62)? => I need also created_at and updated_at as they'can't be null – Mathieu Sep 04 '15 at 20:37
created_at and updated_at are populated automatically by Rails – dimakura Sep 04 '15 at 20:38
yes usually, but here transfering data on a after_create method seems not to do it. if i remove created_at and updated_at form the query, here is what I get: "ERROR: null value in column "created_at" violates not-null constraint DETAIL: Failing row contains (170, not_yet_clicked, null, null, 157, null, null, null, null)." – Mathieu Sep 04 '15 at 20:42
tx for your help, i've pout my latest code with your feedback: self.id now is working but I get an error => see my EDIT – Mathieu Sep 04 '15 at 20:43
I think by the way, I should put outside the query like a constant : TIME= Time.zone.now like this it won't be calculated at each 120,000 times. But I don't know how to do it. – Mathieu Sep 04 '15 at 20:46
I use postgresql, not mysql , if it's important – Mathieu Sep 04 '15 at 20:52

score 1 · Answer 2 · answered Sep 04 '15 at 20:22

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

ERROR: missing FROM-clause entry for table "self" (Rails 4 Active Record transaction, postgresql 9.4))

2 Answers2

Linked