Conditions with whitespaces in activerecord find

Question

So i am trying to do this

Order.find :all, :conditions => "org = 'test     org'"

what ends up firing is

SELECT * FROM `orders` WHERE (org = 'test org')

the whitespace in the argument gets stripped. what am i missing.. im really stumped here. please help!

Answer 1

This is not an issue with rails, but rather a way that mysql behaves.

For example:

mysql> select first_name from users where first_name = "George                    ";
+------------+
| first_name |
+------------+
| George     |
| George     |
+------------+
2 rows in set (0.00 sec)

Here's one way to fix it:

mysql> select first_name from users where BINARY(first_name) = BINARY("George                    ");
Empty set (0.00 sec)

mysql> select first_name from users where BINARY(first_name) = BINARY("George");
+------------+
| first_name |
+------------+
| George     |
| George     |
+------------+
2 rows in set (0.00 sec)

Good luck!

Answer 2

First turn on the general quey log in MySQL and see whether the query, as received and executed by MySQL itself, is correct.

This is because Rails will squeeze all duplicate whitespace from its own query logs (when running in development mode):

  # /rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
  def log_info(sql, name, ms)
    if @logger && @logger.debug?
      name = '%s (%.1fms)' % [name || 'SQL', ms]
      @logger.debug(format_log_entry(name, sql.squeeze(' ')))
    end
  end

If the query sent to MySQL, according to the MySql query log itself, is still incorrect, try again with:

Order.find :all, :conditions => [ 'org = ?', 'test     org' ]