Rail的ActiveRecord find_each与DISTINCT选择 [英] Rail's ActiveRecord find_each with DISTINCT select

问题描述

我想获取数据库中所有唯一电子邮件的列表，并使用 find_each 批量处理它们。

I want to get a list of all unique emails I have in my database and process them in batch using find_each.

下面的代码可以正常工作，直到要处理的记录超过1000条为止。然后它在第1000条记录之后中断，并显示错误消息自定义选择子句中未包含主键

The code below works fine until it has more then 1000 records (batch size) to process. Then it breaks after the 1000th record with the error message Primary key not included in the custom select clause

Tourist.select('DISTINCT email').where("DATE(created_at) = ?", Date.today-  1).find_in_batches do |group|
    something
end

所以，我该如何链接所有这些东西：

So, how can I chain all this:

• 我只需要一个特定字段（电子邮件）


• 我需要它们是唯一的


• 我需要一个where子句


• 我需要find_each

• I only need a specific field (email)
• I need them to be unique
• I need a where a clause
• I need a find_each

推荐答案

您必须手动执行循环 limit 和 offset

You have to do it manually with a loop limit and offset

batch_size = 1000
offset = 0

loop do
  emails = Tourist.where("DATE(created_at) = ?", Date.today-1).select('DISTINCT email').limit(batch_size).offset(offset)
  emails.each do |email| 
    # your stuff
  end 

  break if emails.size < batch_size
  offset += batch_size
end

当然只有在以下情况下才需要该请求将检索大量电子邮件。否则，只需使用 Tourist.where（condition）.pluck（'DISTINCT email'）。每个{| email |您的东西}

Of course this is needed only if the request will retrieve a large number of emails. Otherwise simply use Tourist.where(condition).pluck('DISTINCT email').each { |email| your stuff }

这篇关于Rail的ActiveRecord find_each与DISTINCT选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！