Using `CONCAT` w/ Arel in Rails 4

Question

I have a User model that has first_name and last_name attributes. Using Arel I would like to perform a full name search using CONCAT. I've read the post at How do I use functions like CONCAT(), etc. in ARel? which gives me indication that this is possible but I can't quite get the syntax right. So far I have

class User < ActiveRecord::Base
  def self.search(query)
    concat = Arel::Nodes::NamedFunction.new 'concat', [arel_table[:first_name], arel_table[:last_name]]
    where ...?
  end
end

score 18 · Accepted Answer · answered Mar 16 '15 at 17:17

18

With the latest Arel it's required to use Arel::Nodes.build_quoted(' ') instead of just String (' '). So the answer nowadays is:

SEPARATOR = Arel::Nodes.build_quoted(' ')

Arel::Nodes::NamedFunction.new(
  'concat',
  [arel_table[:first_name], SEPARATOR, arel_table[:last_name]]
)

answered Mar 16 '15 at 17:17

sharipov_ru

643
4
11

`Arel::Nodes.build_quoted(' ')` is defined in Arel >= 6, it didn't work for me (Rails 4.0). Simply replace it with `' '` in that case. – jibai31 Dec 08 '17 at 09:09

score 8 · Answer 2 · edited Aug 09 '15 at 15:09

8

If you want an equal search

 where(concat.eq("john smith"))

 SELECT "users".* FROM "users" WHERE CONCAT("users"."first_name", "users"."last_name") = 'john smith'

If you want a like search

where(concat.matches("%john smith%"))

 SELECT "users".* FROM "users" WHERE (CONCAT("users"."first_name", "users"."last_name")) ILIKE '%admin%'

There are other methods given in the documentation that you can use

You might want a space in your concat

concat = Arel::Nodes::NamedFunction.new(
 'concat',
  [arel_table[:first_name], 
  ' ', 
  arel_table[:last_name]]
)

edited Aug 09 '15 at 15:09

BryanH

5,826
3
34
47

answered Dec 06 '13 at 20:55

usha

28,973
5
72
93

3

Is there a DB agnostic way to achieve this? Right now I have to check what's my DB adapter (stinks) for this to work in both SQLite: "first_name || ' ' || last_name" and Postgres : SQLite: "CONCAT(first_name, ' ', last_name)" – Jim Jun 13 '14 at 03:17
A concat node [was added](https://stackoverflow.com/questions/20431714/using-concat-w-arel-in-rails-4) to Arel at the end of 2015. – Steve Apr 24 '19 at 14:08
You may have error such like ` *** RuntimeError Exception: unsupported: String` afterwards you run the second example, and to fix this use Arel::Nodes.build_quoted(' ') instead. – Baran Yeni Oct 31 '22 at 19:24

Using `CONCAT` w/ Arel in Rails 4

2 Answers2