Ruby: Ideas to convert Arel.sql to Arel::Nodes::Case?

Question

I'm trying to refactor a piece of code (and to improve my understanding of Arel::Nodes::Case as well) and I would like to convert this Arel.sql statement

    Arel.sql(default_order)
    def default_order
        "CASE
          WHEN applications.status = 'new' AND owner_id IS NULL THEN '1'
          WHEN applications.is_priority = '1' AND is_read = '0' AND owner_id = '#{current_user.id}'  THEN '2'
          WHEN applications.is_priority = '1' THEN '3'
          ELSE '4'
         END, applications.code"
    end

into an Arel::Nodes::Case but I'm not getting any good result. Any ideas that could help?

Many thanks in advance.

UPDATE

I did this approach, but I don't like it at all. Too difficult to read and understand:

  def default_order
    arel_applications = Application.arel_table
    Arel::Nodes::Case.new
        .when((arel_applications[:status].eq('new'))
                  .and(arel_applications[:owner_id].eq(nil)), 1)
        .when((arel_applications[:is_priority].eq(1))
                  .and(arel_applications[:is_read].eq(0))
                  .and(arel_applications[:owner_id].eq(current_user.id)), 2)
        .when(arel_applications[:is_priority].eq(1), 3)
        .else(4).to_sql
  end

Even so, I'm not sure how to get that applications.code that is used in the SQL to order the records.

Answer 1

You cannot always expect Arel to be pretty. Arel is a very complex and flexible query assembler and a side effect of this complexity is often verbosity.

That being said using Arel over raw sql is always preferential in my opinion because it is sanitary, dynamic, and database agnostic.

We can alter your update to be a bit more readable by utilizing the then method rather than a second argument to when. This will cause the code to read a lot more like a CASE statement such as .when(condition).then(value).

The alteration would look like this:

  def default_order
    arel_applications = Application.arel_table
    case_stmnt = Arel::Nodes::Case.new
        .when(arel_applications[:status].eq('new').and(
                 arel_applications[:owner_id].eq(nil)
             )
          ).then(1)
        .when(arel_applications[:is_priority].eq(1).and(
                arel_applications[:is_read].eq(0).and(
                  arel_applications[:owner_id].eq(current_user.id)
                )
              )
          ).then(2)
        .when(arel_applications[:is_priority].eq(1)
          ).then(3)
        .else(4)
  end

Now to deal with the second part of the question "Even so, I'm not sure how to get that applications.code that is used in the SQL to order the records."

we can use Arel::Nodes::Grouping to handle this:

def default_order
    arel_applications = Application.arel_table
    case_stmnt = Arel::Nodes::Case.new
        .when(arel_applications[:status].eq('new').and(
                 arel_applications[:owner_id].eq(nil)
             )
           ).then(1).
        .when(arel_applications[:is_priority].eq(1).and(
                arel_applications[:is_read].eq(0).and(
                  arel_applications[:owner_id].eq(current_user.id)
                )
              )
          ).then(2)
        .when(arel_applications[:is_priority].eq(1)
          ).then(3)
        .else(4)
    Arel::Nodes::Grouping.new(case_stmnt,arel_applications[:code])
  end

This will result in the following SQL:

(CASE 
  WHEN [applications].[status] = N'new' AND [applications].[owner_id] IS NULL THEN 1 
  WHEN [applications].[is_priority] = 1 AND [applications].[is_read] = 0 AND [applications].[owner_id] = 1 THEN 2 
  WHEN [applications].[is_priority] = 1 THEN 3 
  ELSE 4 END, 
[applications].[code])

Then you can just use this as an argument to order (no need to convert to_sql as rails will take care of this for you e.g. Application.order(default_order)

You can also append sort direction to both the case_stmnt and the arel_applications[:code] column e.g. case_stmnt.asc or case_stmnt.desc