I'm using MySQL and upgrading my application to Rails 7. In doing so, I've to refactor some order-related queries in my scope methods.
Before of Rails 7 I was using the following scope method (call it the "original scope method"):
scope.order(sanitize_sql_array(["CASE articles.author_id WHEN ? THEN 1 ELSE 2 END", author.id]))
In Rails 7 the above statement raises the error:
ActiveRecord::UnknownAttributeReference (Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "CASE articles.author_id WHEN '3784' THEN 1 ELSE 2 END". This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql().):
What in Rails 7 might come in handy in these cases is the newly added method in_order_of. So, the original scope method might become:
scope.in_order_of(:author_id, [author.id])
However this in_order_of method adds to the SQL query a (unwanted) WHERE clause i.e. the "AND 'articles'.'author_id'" part in the following query:
SELECT `articles`.* FROM `articles`
WHERE (articles.some_other_column >= 10) AND `articles`.`author_id` IN (3784)
ORDER BY FIELD(`articles`.`author_id`, 3784) DESC
The (unwanted) effect of the additional WHERE clause is the removal from the query result of all the records that were present when I was using the original scope method before of Rails 7 i.e in the above example, author ids other than 3784 are ignored but should be present in the query result.
How can I build/refactor in Rails 7 the scope method equivalent to the original "CASE-WHEN-THEN-ELSE-END" that I was using before of Rails 7 in order to order records by the given author id?
It's pretty easy to call any function with Arel:
scope.order(
Arel::Nodes::NamedFunction.new('FIELD', [arel_table[:author_id], author.id]).desc
)
Arel::Nodes::NamedFunction.new takes an function name and an array of arguments.