Database-independent SQL String Concatenation in Rails

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 stringdef 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('||')  endend

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.

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  endend

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)  endend

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.

If you want something Rails neutral, you're going to need to return the values you want concatenated and do that once the data has been delivered to rails (or do it in rails before you give it to the database).

It looks like Mysql uses CONCAT(), Postgres ||, Oracle CONCAT() or ||, T-SQL +.

Any rails abstraction of the same would have to take place at a point where you could just be doing concatenation using regular Ruby, or I've completely misunderstood the question.