Database-independent SQL String Concatenation in Rails

Question

I want to do a database-side string concatenation in a Rails query, and do it in database-independent way.

SQL-92 specifies double-bar (||) as the concatenation operator. Unfortunately it looks like MS SQL Server doesn't support it; it uses + instead.

I'm guessing that Rails' SQL grammar abstraction has solved the db-specific operator problem already. If it does exist, how do I use it?

Answer 1

I had the same problem and never came up with anything that was built into Rails. So I wrote this little method.

# Symbols should be used for field names, everything else will be quoted as a string
def db_concat(*args)

  adapter = configurations[RAILS_ENV]['adapter'].to_sym
  args.map!{ |arg| arg.class==Symbol ? arg.to_s : "'#{arg}'" }

  case adapter
    when :mysql
      "CONCAT(#{args.join(',')})"
    when :sqlserver
      args.join('+')
    else
      args.join('||')
  end

end

I'm thinking somebody should really write some sort of SQL helper plugin that could automatically format simple SQL expressions based using the correct functions or operators for the current adapter. Maybe I'll write one myself.

Answer 2

It hasn't received much usage yet but I wrote the following code which seems to solve the problem. This monkey-patches the adapters to have a method to support it:

module ActiveRecord
  module ConnectionAdapters
    class AbstractAdapter

      # Will return the given strings as a SQL concationation. By default
      # uses the SQL-92 syntax:
      #
      #   concat('foo', 'bar') -> "foo || bar"
      def concat(*args)
        args * " || "
      end

    end

    class AbstractMysqlAdapter < AbstractAdapter

      # Will return the given strings as a SQL concationation.
      # Uses MySQL format:
      #
      #   concat('foo', 'bar')  -> "CONCAT(foo, bar)"
      def concat(*args)
        "CONCAT(#{args * ', '})"
      end

    end

    class SQLServerAdapter < AbstractAdapter

      # Will return the given strings as a SQL concationation.
      # Uses MS-SQL format:
      #
      #   concat('foo', 'bar')  -> foo + bar
      def concat(*args)
        args * ' + '
      end

    end
  end
end

With this you should be able to do the following in your code:

class User < ActiveRecord::Base

  def self.find_by_name(name)
    where("#{connection.concat('first_name', 'last_name')} = ?", name)
  end

end

This outputs the following SQL query on a SQL-92 database (Oracle, SQLite, PostgreSQL):

SELECT * FROM users WHERE first_name || last_name = ?

For MySQL it outputs:

SELECT * FROM users WHERE CONCAT(first_name, last_name) = ?

For SQL Server it outputs

SELECT * FROM users WHERE first_name + last_name = ?

Obviously you could extend this concept to other database adapters.