2

I am using FuelPHP and MySQL, and would like to use the ORM to query with a case insensitive like query against a column with a case sensitive collation.

For example, in my orm model, I'd like to do something like this:

public static function search_by_name($name)
{
    return self::query()->where('name', 'like', '%' . $name . '%')->get();
}

The problem here is that when I search for $name = 'john', the expression will not match rows where the column includes John and vice-versa.

Anyone know how to get around this?

johncorser
  • 9,262
  • 17
  • 57
  • 102
  • 1
    mysql string comparisons are usually case-insensitive, unless you're using a case-sensitive collation or a `binary` comparison. worst case, you can always coerce-to-lower and compare that, e.g. `lower(foo) LIKE CONCAT('%', lower('bar'), '%')` – Marc B Jul 07 '15 at 20:29
  • 1
    MySQL syntax allows for the `COLLATE` keyword to be used in a conjunction with a string comparison. For example, if `name` is latin1 characterset, with a case sensitive collation, you can get a case insensitive comparison with a predicate like this: **`name LIKE _latin1'%john%' COLLATE latin1_swedish_ci`**. (We want the literal to match the characterset of the column. I was just using latin1 as an example.) – spencer7593 Jul 07 '15 at 20:31
  • MarcB yes, I am using a case-sensitive collation. And I'd usually do what @spencer7593 suggested but I'm using an ORM, not pure sql – johncorser Jul 07 '15 at 20:38
  • 1
    As far as how to get a predicate of the form shown by MarcB, or the form demonstrated in my previous comment, via FuelPHP... that may only be available using a `Custom SQL` query. But you could experiment with some expressions. See how big a smokeball that makes.... **`->where('lower(name)','like','%'.strtolower($name).'%')->`** – spencer7593 Jul 07 '15 at 20:52
  • 1
    If FuelPHP wraps the column names in backticks in the generated SQL, then the expression won't work... the generated SQL will be **`WHERE \`lower(name)\` LIKE `**. And unless you've created a column named **`lower(name)`**, that's going to make a good sized smokeball. – spencer7593 Jul 07 '15 at 21:00
  • That was one of the first things I tried, unfortunately, the orm does wrap column names in backticks so it have me "column lower(name) does not exist" – johncorser Jul 08 '15 at 14:30
  • You can solve that using DB::expr(). – WanWizard Sep 16 '16 at 10:48

1 Answers1

0

The workaround is to use the "Custom SQL" query feature available in FuelPHP, to bypass the syntax limitations, and get a SQL query string passed to the database.

spencer7593
  • 106,611
  • 15
  • 112
  • 140