Rails 3 ORDER BY FIELD和最后 [英] Rails 3 ORDER BY FIELD and last

问题描述

你好，我在Rails 3.2和排序方面遇到问题。

当要按字段排序集合时，调用 .last ActiveRecord 的行为很奇怪...

 > ;> User.order（ FIELD（id，'1'））
用户负载（0.4ms）选择`users .. * FROM`users` ORDER BY FIELD（id，'1'）
 = > [] 
>> User.order（ FIELD（id，'1'））。first 
用户负载（0.4ms）选择`users`。* FROM`users` ORDER BY FIELD（id，'1'）LIMIT 1 
 =>无
>> User.order（ FIELD（id，'1'））。last 
用户负载（0.3ms）选择`users`。* FROM`users` ORDER BY FIELD（id DESC，'1'）DESC LIMIT 1 
 Mysql2 :: Error：您的SQL语法有错误； 
  

如您所见，在关系上最后调用添加 DESC 两次，但应该只将其放在整个 ORDER BY FIELD 之后，而不是放在括号内。

有人知道怎么做吗？

谢谢！

解决方案

这很旧，但是今天我遇到了这个问题，发现了一种不需要使用额外范围的解决方法。

创建一个SQL函数将调用包装到 FIELD ，这样在 order 的调用中就不会出现逗号。这是PostgreSQL，但翻译非常简单：

  CREATE FUNCTION字段1（值文本）以$$ 
开始返回文本
 RETURN FIELD（value，1）; 
 END； 
 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ ActiveRecord中的$ c>现在可以正常工作：
  User.order（'field1（id）'）。last 
  
 
Hello I have an issue with Rails 3.2 and ordering.

When wanting to order a collection by a field, when calling .last ActiveRecord behaves weirdly...
   >> User.order("FIELD(id, '1')")
   User Load (0.4ms)  SELECT `users`.* FROM `users` ORDER BY FIELD(id, '1')
   => []
   >> User.order("FIELD(id, '1')").first
   User Load (0.4ms)  SELECT `users`.* FROM `users` ORDER BY FIELD(id, '1') LIMIT 1
   => nil
   >> User.order("FIELD(id, '1')").last
   User Load (0.3ms)  SELECT `users`.* FROM `users` ORDER BY FIELD(id DESC, '1') DESC LIMIT 1
   Mysql2::Error: You have an error in your SQL syntax;
As you can see calling last on the relation add DESC twice, but it should have only put it after the whole ORDER BY FIELD, and not inside the parenthesis..

Do anyone has an idea how to do it?

Thanks!
解决方案
This is old but I run into this problem today and found a workaround that doesn't require the use of extra scopes.

Create a SQL function to wrap the call to FIELD, this way there will be no comma in the invocation to order. This is PostgreSQL but the translation is straightforward: 
CREATE FUNCTION field1(value text) RETURNS text AS $$
BEGIN
  RETURN FIELD(value, 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE
Calling last in ActiveRecord now works:
User.order('field1(id)').last


                        
这篇关于Rails 3 ORDER BY FIELD和最后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！