Database-Independent SQL String Concatenation in Rails

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 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.

How do I concat to a text field in ActiveRecord?

There isn't anything in ActiveRecord to support CONCAT. However you can write a custom SQL query that uses CONCAT, like so:

ActiveRecord::Base.connection.execute(sql)

Or you could adjust your mysql settings:

 mysqld --max_allowed_packet=16M

Or the best solution has been addressed already:
Database-independent SQL String Concatenation in Rails

Concatenate variable and string with Arel on join

It seems Arel::Nodes::SqlLiteral does the job:

node = Node.arel_table
job = Job.arel_table

lit = Arel::Nodes::SqlLiteral.new("_______%") 

node.join(job).on(node[:id].matches(lit))

How to concat two fields in Postgresql

concatenation in Postgres like in SQLite: ||

(users.first_name || ' ' || users.last_name)

User.where('((users.first_name || ' ' || users.last_name) ILIKE ?) OR (users.first_name ILIKE ?) OR (users.last_name ILIKE ?)', "%#{search}%", "%#{search}%", "%#{search}%")

Hibernate CriteriaAPI and like operator. Swap operands + database independent concatenation - is it possible?

You could write your own Criterion implementation, which would generate a SQL clause similar to the one you have in your question, except it would use the dialect associated with the criteria query to get the appropriate concat function and delegate the concatenation to this database-dependant concat function.

Lambda query multiple strings (ruby on rails)

If you are going to be retrieving all the tickets anyway, this would be more easily achieved by sorting in rails. For example

tickets = Ticket.where(:some_condition => some_value)
tickets_by_status = []
['open', 'paused', 'closed'].each do |status|
  tickets_by_status.concat tickets.select{|ticket| ticket.status == status}
end

Instead of abstracting this using a scope, consider creating a class method on the Ticket model, something like all_by_status.

[EDIT]

If you don't need the tickets to appear in the order you've specified you can simply order by status. This will sort by status alphabetically (closed, open, paused)

Ticket.order(:status)

---

Related Topics