ruby 每个循环按 sql order 查询结果的顺序 [英] ruby each loop in order on sql order query result

问题描述

我的控制器中有一个完美运行的查询:

I have a query in my Controller that works perfectly:

@klasses_mon = Klass.order(:start).where(day: 'MON').find_each

@klasses_mon = Klass.order(:start).where(day: 'MON').find_each

我的结果是(在我看来由 <%= @klasses_mon.inspect %> 显示):

my result is (shown by <%= @klasses_mon.inspect %> in my view):

#<Enumerator: #<ActiveRecord::Relation 
[#<Klass id: 9, name: "Cycling", teacher: "Tomek", day: "MON", start: 510, duration: 45>,
#<Klass id: 8, name: "LBT", teacher: "Monia", day: "MON", start: 600, duration: 60>, 
#<Klass id: 11, name: "HIIT", teacher: "Aga", day: "MON", start: 930, duration: 45>]>
:find_each({:start=>nil, :finish=>nil, :batch_size=>1000, :error_on_ignore=>nil})>

但我试图在每个循环中显示它.出于某种原因，它不再订购了.看起来每个循环都没有从我的查询结果中保留顺序:

But I am trying to display it in each loop. For some reason it is not ordered anymore. Looks like each loop does not keep the order from my query result:

<% @klasses_mon.each do |k| %>
    <p><%= k.teacher %>,
    <%= k.name %>
    START: <%= k.start/60 %>:<%= k.start%60 %>
<% end %>

结果:

莫妮亚，LBT 开始:10:0

Monia, LBT START: 10:0

托梅克，骑自行车开始:8:30

Tomek, Cycling START: 8:30

Aga，HIIT 开始:15:30

Aga, HIIT START: 15:30

我该怎么做?

推荐答案

来自 精美手册:

find_each(开始:nil，结束:nil，batch_size:1000，error_on_ignore:nil)
[...]
注意:无法设置顺序.这在主键(id ASC")上自动设置为升序，以使批量排序工作.这也意味着此方法仅在主键可排序(例如整数或字符串)时有效.

find_each(start: nil, finish: nil, batch_size: 1000, error_on_ignore: nil)
[...]
NOTE: It's not possible to set the order. That is automatically set to ascending on the primary key ("id ASC") to make the batch ordering work. This also means that this method only works when the primary key is orderable (e.g. an integer or string).

因此 find_each 被明确记录为忽略您尝试使用的任何顺序.

So find_each is explicitly documented to ignore any ordering that you try to use.

find_each 不使用 LIMIT 和 OFFSET 在结果集中移动批处理窗口，因为随着 OFFSET 的增加，这往往会非常昂贵，而是按主键排序并包含 <代码>id>WHERE 子句中的 last_one 条件设置批处理的开始，并使用 LIMIT 子句设置批处理大小.按 PK 排序和查询 PK 与 LIMIT 子句一样通常都很便宜.

find_each doesn't use LIMIT and OFFSET to move the batch window through the result set as that tends to be very expensive as the OFFSET increases, instead it orders by the primary key and includes a id > last_one condition in the WHERE clause to set the start of the batch and a LIMIT clause to set the batch size. Ordering by the PK and querying on the PK are both generally inexpensive as is a LIMIT clause.

find_each 是此工作的错误工具，find_each 用于批处理工作，但您只是显示一个简短的记录列表，因此您需要一个简单的:

find_each is the wrong tool for this job, find_each is for batch work but you're just displaying a short list of records so you want a simple:

@klasses_mon = Klass.order(:start).where(day: 'MON')

这篇关于ruby 每个循环按 sql order 查询结果的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！