ActiveRecord随机向ORDER BY添加“DESC”

When I call the following:

当我打电话给以下人时:

organization = Organization.first
organization.members.order("SUBSTRING_INDEX(SUBSTRING_INDEX(members.name, ' ', 3), ' ', -1)").last

Gives me the following 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, ' ' DESC, 3) DESC, ' ' DESC, -1) DESC LIMIT 1' at line 1: 
SELECT  `members`.* FROM `members`  WHERE `members`.`organization_id` = 2
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(members.name DESC, ' ' DESC, 3) DESC, ' ' DESC, -1) DESC LIMIT 1

Issue is in this statement:

问题出在本声明中:

SUBSTRING_INDEX(SUBSTRING_INDEX(members.name DESC, ' ' DESC, 3) DESC, ' ' DESC, -1) DESC LIMIT 1

It adds "DESC" randomly to the ORDER BY statement. Is this an AR issue or am I doing something wrong?

它会在ORDER BY语句中随机添加“DESC”。这是AR问题还是我做错了什么?

Note: This only happens when I call last. I'm using ActiveRecord 4.1.1.

注意:这只发生在我上次呼叫时。我正在使用ActiveRecord 4.1.1。

1 个解决方案

#1

Something deep within the bowels of ActiveRecord or AREL is making assumptions about what commas mean in an SQL ORDER BY clause. The assumption is that the ORDER BY is a string that looks like expr1, expr2, ... with no commas inside the individual expressions.

ActiveRecord或AREL内部深处的某些内容正在假设SQL ORDER BY子句中的逗号含义。假设ORDER BY是一个看起来像expr1,expr2,......的字符串,各个表达式中没有逗号。

When you add the .last, AR will eventually get around to calling reverse_order on the query. reverse_order makes dumb assumptions about the format of the ORDER BY clause, it (or something it calls) assumes that an SQL ORDER BY looks like expr1, expr2, ... with no commas inside the individual expressions. When reverse_order attempts to reverse the order, it blindly splits the ORDER BY string on commas and pastes the pieces back together with DESCs in various places. This of course doesn't work if your ORDER BY uses "advanced" things like function calls.

当你添加.last时,AR最终会在查询中调用reverse_order。 reverse_order对ORDER BY子句的格式做出愚蠢的假设,它(或它调用的东西)假定SQL ORDER BY看起来像expr1,expr2,......在各个表达式中没有逗号。当reverse_order尝试颠倒顺序时,它会盲目地在逗号上拆分ORDER BY字符串,并将各个部分与DESC粘贴在一起。如果您的ORDER BY使用函数调用等“高级”内容,这当然不起作用。

You could add that function call to your SELECT and include an alias for it and then .order('your_column_alias') to hide the commas from AR.

您可以将该函数调用添加到SELECT并为其包含别名,然后使用.order('your_column_alias')来隐藏AR中的逗号。

You could also pretend that last doesn't exist and use first instead:

您也可以假装最后不存在并使用第一个:

organization.members.order("SUBSTRING_INDEX(...) DESC").first

This of course assumes that you know the structure of the ORDER BY.

这当然假设您知道ORDER BY的结构。

#1