AR/Arel - How can i compose a query to SELECT a conditional CONCAT of columns

user_3357635 0
Viewed 158

I've got a model method that conditionally concatenates the user's username ("login") and real name, if they've saved a real name - otherwise it just shows the username. I'd like to rewrite the query in ActiveRecord or Arel.

It looks like I should use an Arel::Nodes::NamedFunction. But i don't understand how to do the conditional concatenation with a named function. (Does Arel know about "if"? I can't find any reference in the docs.)

  def primer_values
    connection.select_values(%(
      SELECT CONCAT(users.login,
                    IF(users.name = "", "", CONCAT(" <", users.name, ">")))
      FROM users
      ORDER BY IF(last_login > CURRENT_TIMESTAMP - INTERVAL 1 MONTH,
                  last_login, NULL) DESC,
                contribution DESC
      LIMIT 1000
    )).uniq.sort
  end

There's also similarly a conditional in ORDER BY.

arel ruby-on-rails-5 activerecord ruby-on-rails sql
1 Answers

While generally I abhor Raw SQL in rails given this usage I'd leave it as is. Although I might change it to something a bit more idiomatic like.

User
  .order(
    Arel.sql("IF(last_login > CURRENT_TIMESTAMP - INTERVAL 1 MONTH,last_login, NULL)").desc,
    User.arel_table[:contribution].desc)
  .limit(1000)
  .pluck(Arel.sql(
    'CONCAT(users.login,
      IF(users.name = "", "", 
        CONCAT(" <", users.name, ">")))'))
  .uniq.sort

Converting this to Arel without abstracting it into an object of its own will damage the readability significantly.

That being said just to give you an idea; the first part would be 3 NamedFunctions

  1. CONCAT
  2. IF
  3. CONCAT
Arel::Nodes::NamedFuction.new(
  "CONCAT",
  [User.arel_table[:name],
   Arel::Nodes::NamedFuction.new(
     "IF",
     [User.arel_table[:name].eq(''),
      Arel.sql("''"),
      Arel::Nodes::NamedFuction.new(
         "CONCAT",
         [Arel.sql("' <'"),
          User.arel_table[:name],
          Arel.sql("'>'")]
      )]
  )]
)

A NamedFunction is a constructor for FUNCTION_NAME(ARG1,ARG2,ARG3) so any SQL that uses this syntax can be created using NamedFunction including empty functions like NOW() or other syntaxes like LATERAL(query).

user_19782510
Related