CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4
Mathieu
Mathieu

Reputation: 4787

Rails 4/postgresql - Send data to another randomly chosen rows of another table

I have a Ruby on Rails 4 app and postgresql9.4 as database.

I have a Deal, DealPrize and Prize models.

Prizes have a quantity and a name. For example prize1 has name dvd and quantity 67. DealPrizes have a column deal_id and prize_id

class Deal
  has_many :prizes, dependent: :destroy   
  has_many :deal_prizes, dependent: :delete_all
end

class Prize
  belongs_to :deal, :foreign_key => 'deal_id'
  has_many   :deal_prizes,  dependent: :destroy  
end

class DealPrize
  belongs_to :deal,  :foreign_key => 'deal_id'       
  belongs_to :prize, :foreign_key => 'prize_id'
end

Database structure:

create_table "deal_prizes", id: :bigserial, force: :cascade do |t|
    t.string   "name",                    
    t.integer  "deal_id"
    t.integer  "prize_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
end

create_table "prizes", force: :cascade do |t|
    t.string   "prize_name",         limit: 255
    t.integer  "quantity"
    t.integer  "deal_id"
    t.datetime "created_at",    null: false
    t.datetime "updated_at",    null: false    
end

add_index "prizes", ["deal_id"], name: "index_prizes_on_deal_id", using: :btree


create_table "deals", force: :cascade do |t|
    t.string   "name",          limit: 255
    t.string   "description",   limit: 255    
    t.datetime "created_at",    null: false
    t.datetime "updated_at",    null: false   
end

I'm gonna take an example to explain what I want and so far fail to achieve:

Note: I don't want to find them and write on rows that are consecutive: i need random rows.

Note: the number of rows in DealPrize is superior to 2 millions so need for performance is required. I would rather use raw sql than active record and will try to not use Rand and random functions directly on the whole table like this guy advice not to http://www.adamwaselnuk.com/projects/2015-03-28-here-be-taverns.html

"The problem is these can lead to performance issues on larger tables because when you say "Hey database order the table randomly and then give me the first row" the table says "Okay, I am going to go through every single row and assign it a random number and then limit my selection to one". Asking for one thing results in a computation on every single row in the table!"

I'm totally new to Rails so I could only explain/map the different steps needed on Models/deal.rb

# 1. find the first prize where deal_id= seld.id
# and take each of the prizes quantity and id ofr the next steps
def find_prizes_for_this_deal
  Prize.find_by deal_id: self.id  # here I could only find one, don't know how to find many prizes at once and keep both id and quantity
end

# 2. choose Deal prize's table randomly chosen rows (created for the same
# deal attached to prizes) where deal_id=self.id and prize_id = empty    
def DealPrize.random
  # trick found here http://www.adamwaselnuk.com/projects/2015-03-28-here-be-taverns.html, in order not to use the poor-performance rand() methodon the whole table
  offset(rand(count)).first.where(["deal_id = ?", self_id] && prize_id= nil)
end

#3. send the prizes selected in step 1. into the randomly chosen rows of 
# DealPrizes in step 2
def NO IDEA here how to do this at
end

How to make this work ?

Upvotes: 0

Views: 58

Answers (2)

patrickh003
patrickh003

Reputation: 168

There is a lot that needs to be done for this problem. I can help you out with most and point you in the right direction.

The first part has been answered

<pre><code>deal = Deal.find(5)
prizes = deal.prizes
</code></pre>

You can loop through prizes



    prizes.each do |prz|
      d_id = prz.deal_id
      p_id = prz.id
      quantity_count = prz.quantity
    end

To get the quantity of the first prize

quantity_count = prizes[0].quantity

Or get one prize by id

prz = prizes.where(id: 5)

I suggest creating two new arrays that will store random numbers to use in your queries. Get the last record id from deal_prizes to be your max number.

max = DealPrize.order("id").last.id

Based on the quantity count, loop to populate array with random numbers

first_prize = []
(1..quantity_count).each do |n|
   first_prize << rand(max)
end

You can use that array the find or update records

DealPrize.where(id: first_prize).update_all(deal_id: d_id, prize_id: p_id)

Or if your going to hardcode it

"UPDATE deal_prizes SET deal_id = #{d_id}, prize_id = #{p_id} WHERE id IN ( #{first_prize.join(',')} )"

Be mindful of updating records with prize_id not null or zero (however you indicate it). You can run queries till you find exactly 67 records that have prize_id is null.

good_rows = DealPrize.where(id: first_prize).where("prize_id IS NULL")
bad_rows = first_prize - good_rows

and then replace ids in bad_rows by randomly generating new ids and query again. Continue doing that till you find all good rows then update by adding your new set of good rows with the existing one perfect_first_prize = good_rows + new_good_rows

Upvotes: 1

Aleksey Shein
Aleksey Shein

Reputation: 7482

Your question is very complex and confuses me a lot. Let's go step by step.

I need to select all the prizes associated with Deal id= 5.

That's easy. 

deal = Deal.find(5)
prizes = deal.prizes

Upvotes: 1

Related Questions