Question 20364 in Mysql Question posted in
The official MySQL documentation can be found here.

Refactoring Rails scope method CASE-WHEN-THEN-ELSE-END in MySQL

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Found a solution by wrapping attributes (known-safe values) in Arel.sql() as advised in the error message.

    scope.order(Arel.sql("FIELD(articles.author_id, #{author.id}) DESC"))

    So the SQL query will be:

    SELECT `articles`.* FROM `articles` 
WHERE (articles.some_other_column >= 10)
ORDER BY FIELD(`articles`.`author_id`, 3784) DESC

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search