Laravel casts strings to numbers in DB queries?

user_4885772 0
Viewed 92

I have a table of players with a field called name (which is a string, of course). When adding a player to the database, I check for uniqueness using a custom rule:

public function passes($attribute, $value)
{
    return !($this->players->where('name', $value)->count());
}

This works most of the time. I noticed, however, that strings are cast to numbers whenever possible. For example, if there is a player with the name "123", then I cannot add a new player whose name is "0000123" or "123." or other such variations with leading or trailing zeroes. Or, for example, if the DB has a player called "9.87e2", then I can't add "987".

As a sanity check, I logged gettype($value) inside the function and confirmed that it is always a string. I then tried replicating the issue in a SQL sandbox, but everything worked as expected. Does that mean this is a bug in Laravel?

laravel casting sql
1 Answers

The issue doesn't seem to be casting, but rather loose comparison on name when using ->where() on the resulting Collection vs ->where() on the Database layer. Compare:

$this->players->where('name', $value)->count();

In the above, $this->players will be a Collection of all Player records from the Database associated with $this (another Model I assume). This is the equivalent of something like:

$players = collect([
  ['id' => 1, 'name' => '123'],
  ['id' => 2, 'name' => '234'],
  // ...
]);

$players->where('name', '123')->count();    // 1
$players->where('name', '000123')->count(); // 1 (That's odd...)
$players->where('name', '234')->count();    // 1
$players->where('name', '345')->count();    // 0
// ... Ect etc.

In the above examples, you can see that the "loose" comparison on name, using '00123' and '123' both returns 1. This is problematic, as the Database can handle '000123' and '123' as distinct entities, but querying against a Collection cannot.

To get around this, we should query directly against the Database, using the players method instead of property (() denotes a method, lack of () denotes a property):

$query = $this->players()->where('name', $value); // Omitted the `count()` for now

At this point in time, $query is a Database Query (Builder instance). We can see the SQL that would be executed by calling ->toSql():

dd($query->toSql());
// SELECT * FROM players WHERE related_id = ? AND name = ?

(Replace related_id with whatever $this represents in the original question).

Chaining the method ->count() will execute this as a SELECT count(...) ... query, instead of counting the length of the Collection.

So, to put it all together; Query directly against the Database using ->players(), execute a ->count() or ->exists() query:

return !$this->players()->where('name', $value)->count();  // 0 or # > 1
// OR
return !$this->players()->where('name', $value)->exists(); // true or false
user_39656310
Related