Query inconsistently works with like on integer column

In my controller, I am using the following query where member_number is a INT MySQl column

WorkHours::query()
                ->when(Request::input('search'), function ($query, $search) {
                    $query->withWhereHas('user', function($query) use ($search) {
                        $query->where('last_name', 'like', "%{$search}%")
                            ->orWhere('first_name', 'like', "%{$search}%")
                            ->orWhere('member_number', 'like', "%{$search}%");
                    });
                })

If I run a query right in MySQL like so

select * from users where member_number like '%1234%';

it works fine, but the above query code doesn't work. But then, I have a similar query in another controller using the Spatie Query Builder using this custom filter

    public function __invoke(Builder $query, $value, string $property)
    {
        $query->where(function (Builder $query) use ($value) {
            $query->where('last_name', 'like', "%{$value}%")
                ->orWhere('first_name', 'like', "%{$value}%")
                ->orWhere('member_number', 'like', "%{$value}%");
        });
    }

and the number works fine. I can't figure out the discrepancy, why it works in a couple places, but not all.

LaryAI

2 years ago

Best Answer

Level 58

The issue you're experiencing might be due to the fact that you're trying to use the like operator on an integer column. While MySQL allows this, Laravel might be handling this differently.

In your case, you're using the withWhereHas method, which is not a standard Laravel method. I think you meant to use whereHas. The whereHas method is used to add constraints to the query based on relationships.

Here's how you can modify your query:

WorkHours::query()
    ->when(Request::input('search'), function ($query, $search) {
        $query->whereHas('user', function($query) use ($search) {
            $query->where('last_name', 'like', "%{$search}%")
                ->orWhere('first_name', 'like', "%{$search}%")
                ->orWhereRaw('CAST(member_number AS CHAR) LIKE ?', "%{$search}%");
        });
    });

In this query, I've used orWhereRaw to manually write the SQL for the member_number condition. This allows us to cast the member_number to a string (using CAST(member_number AS CHAR)) before using the like operator. This should ensure that the like operator works consistently, even when used on an integer column.

Please or to participate in this conversation.