Case insensitive search

[danharrin]

There are a couple of parts to case insensitive search that could benefit an abstraction.

To start with - choosing either like or ilike as the comparison operator, depending on the database driver.

My other pain point is to do with needing to use whereRaw() instead of where, since the column name needs manually wrapping in a lower() function. Ideally, I should be able to search case-insensitively without needing to use a raw query.

[tpetry]

Would this fit your usecase?

$query->where(new CaseInsensitiveLike('mycolumn', new Value('%searchterm%'));

I guess the column should always be lower()-cased, as the column (or expression) could have any specified collation or case sensitivity setting.

[danharrin]

Perfect! I think that CaseSensitiveLike could also be useful to people.

Or even $query->where(new Like('mycolumn', new Value('%searchterm%'), isCaseSensitive: false);

[tpetry]

The problem is that I can't guarantee case sensitive like. Because the MySQL collation of that string may be case-insensitive, so like will always behave case insensitive. Or for PostgreSQL a citext text type that is always case insensitive.

[danharrin]

Fair enough 👍

[tpetry]

Many expressions would have to implement case-insensitivity all on their own again and again.

Are you ok with a more verbose approach?

$query->where(new Like(new Lower('mycolumn'), new Value('%searchterm%'));

This one would fit a database-centric approach. Because even on MySQL you can't expect mycolumn to be case-insensitive, so you are explicitly asking it to be of a specific sensitive. Because transparently doing this by hiding the lower function call would render any index on the mycolumn useless.

[danharrin]

Yeah I think that is also perfectly acceptable