代码之家 › 专栏 › 技术社区 › ByteMe

如何避免对此代码进行N+1查询

activerecord ruby-on-rails

1

ByteMe · 技术社区 · 8 年前

我一直在努力找出如何避免使用这段代码中的这么多SQL查询。我通过Shopify的API接收Collect对象,然后检查本地数据库中是否已经有匹配项,如果没有,则使用本地匹配产品和集合中的参数创建一个新的本地Collect对象。我不确定如何使用includes或join,因为我正在进行查找,而不仅仅是在循环中获取值。欢迎任何指点。

  def seed_collects!
    shop = self.with_shopify!
    c = (1..(ShopifyAPI::Collect.count.to_f/250.0).ceil).flat_map do |page|
      ShopifyAPI::Collect.find(:all, :params => {:page => page.to_i, :limit => 150})
    end
    c.each do |collect|
      next if Collect.find_by(shopify_collect_id: collect.id)
      product = Product.find_by(shopify_product_id: collect.product_id.to_s)
      collection = Collection.find_by(shopify_collection_id: collect.collection_id.to_s)
      col = Collect.new(shopify_collect_id: collect.id,
                        position: collect.position,
                        product_id: product.id,
                        collection_id: collection.id)  
      col.save
      puts col.errors.inspect if col.errors.any?
    end
  end

架构

  create_table "collections", force: :cascade do |t|
    t.string   "title"
    t.string   "shopify_collection_id"
    t.string   "collection_type"
    t.datetime "created_at",            null: false
    t.datetime "updated_at",            null: false
    t.integer  "shop_id"
  end

  create_table "collects", force: :cascade do |t|
    t.string   "position"
    t.string   "shopify_collect_id"
    t.datetime "created_at",         null: false
    t.datetime "updated_at",         null: false
    t.integer  "collection_id"
    t.integer  "product_id"
  end

  create_table "products", force: :cascade do |t|
    t.string   "title"
    t.string   "shopify_product_id"
    t.string   "product_type"
    t.string   "tags"
    t.datetime "created_at",         null: false
    t.datetime "updated_at",         null: false
    t.integer  "shop_id"
    t.string   "main_image_src"
  end

模型

class Collect < ActiveRecord::Base
  belongs_to :product
  belongs_to :collection

  delegate :shop, to: :product
  delegate :shopify_product_id, to: :product 
  delegate :shopify_collection_id, to: :collection 
end

class Collection < ActiveRecord::Base
  # verify all items belong to shop
  belongs_to :shop #verify
  has_many :collects, dependent: :destroy
  has_many :products, through: :collects

end

class Product < ActiveRecord::Base
  belongs_to :shop
  has_many :variants, ->{ order(:created_at) }, dependent: :destroy
  has_many :price_tests, dependent: :destroy
  has_many :metrics, ->{ order(:created_at) }, dependent: :destroy 
  has_many :collects, dependent: :destroy
  has_many :collections, through: :collects

1 回复 | 直到 8 年前

1

2

Tai 8 年前

在您的情况下,避免N+1的更好方法是批量获取集合、产品、集合。请尝试以下代码:

def seed_collects!
  shop = self.with_shopify!
  c = (1..(ShopifyAPI::Collect.count.to_f/250.0).ceil).flat_map do |page|
    ShopifyAPI::Collect.find(:all, :params => {:page => page.to_i, :limit => 150})
  end

  shopify_collect_ids    = c.map(&:id)
  shopify_product_ids    = c.map(&:product_id)
  shopify_collection_ids = c.map(&:collection_id)

  collects    = Collect.where(shopify_collect_id: shopify_collect_ids).pluck(:shopify_collect_id, :id)
  collect_ids = Hash[collects]

  products    = Product.where(shopify_product_id: shopify_product_ids).pluck(:shopify_product_id, :id)
  product_ids = Hash[products]

  collections    = Collection.where(shopify_collection_id: shopify_collection_ids).pluck(:shopify_collection_ids, :id)
  collection_ids = Hash[collections]

  c.each do |collect|
    next if collect_ids[collect.id]
    col = Collect.new(shopify_collect_id: collect.id,
                      position: collect.position,
                      product_id: product_ids[collect.product_id.to_s],
                      collection_id: collection_ids[collect.collection_id.to_s])  
    col.save
    puts col.errors.inspect if col.errors.any?
  end
end