是否可以在“顺序"方法子句中使用问号? [英] Is it possible to use question marks in 'order' methods-clauses?

问题描述

我正在使用 Ruby on Rails 3.2，我想知道是否有办法做到以下几点:

I am using Ruby on Rails 3.2 and I would like to know if there is a way to make the following:

@categories.order('user_id = ? DESC', params[:id])
@categories.order('user_id = ? DESC', @user.id)

# Note: It is almost the same as `@categories.where('user_id = ?', params[:id])`
# but for the `order` clause.

以上语句产生此错误:

ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for the 
right syntax to use near '? DESC, ...

推荐答案

如果您尝试根据 user_id 更改顺序，首先返回匹配的那些，您应该使用 CASE 声明.

If you are trying to change the order based on the user_id, returning those that match first, you should be using a CASE statement.

在原始 SQL 中，它看起来像这样:

In raw SQL, it would look like this:

SELECT * FROM Categories ORDER BY CASE user_id WHEN 34 THEN 1 ELSE 2 END

这会将 user_id 为 34 的所有行置于顶部，其余行则保留默认顺序.

That would bring all of rows where user_id is 34 to the top, leaving the rest to default ordering.

现在，就像其他人提到的那样，order 没有任何清理 SQL 的机制，因此您必须在 ActiveRecord::Base 上使用受保护的类方法.

Now, like others have mentioned, order does not have any mechanism to sanitize SQL, so you will have to use the protected class method on ActiveRecord::Base.

它可能看起来像这样:

order_sql = Category.send :sanitize_sql_array, ["CASE user_id WHEN ? THEN 1 ELSE 2 END", some_user_id]

@categories.order(order_sql)

这篇关于是否可以在“顺序"方法子句中使用问号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！