migration

gistfile1.rb

class SetDefaultCanonicalUrlsForCollects < ActiveRecord::Migration
  def change
    Account.find_in_batches(:batch_size => 300) do |accounts|
      execute "commit;"
      accounts.each do |account|
        execute "
          UPDATE collects
          SET is_canonical_url = true
          WHERE id in (
            SELECT DISTINCT ON (product_id) id FROM collects WHERE product_id IN (
                  select id as product_with_no_canonical_id from (
                        select id from products where id IN (
                                select product_id from (select * from collects where account_id = #{account.id}) account_collections WHERE (select count(*) from collects where product_id = account_collections.product_id) > 1 GROUP by product_id)
                            ) products_with_many_collections
                    where (select count(*) from collects where product_id = products_with_many_collections.id AND is_canonical_url is TRUE) = 0  )
            ORDER BY product_id, collection_id ASC
          );
        "
      end
    end
  end
end