HasMany Eager Loading Fails with Binary Keys in WHERE IN Clause

[GokhanKR]

Laravel Version

11

PHP Version

8.2

Database Driver & Version

10.11.2-MariaDB

Description

When using Eloquent’s ::with method to eagerly load a HasMany relationship with binary primary/foreign keys, the generated WHERE IN clause in the relationship query uses string representations of the keys instead of their binary form. This causes the query to return no results, even though the data exists in the database and direct queries with binary values work correctly.

This issue arises because the HasMany relation’s addEagerConstraints method retrieves the parent model’s key via getEagerModelKeys, which seems to cast or process the binary key (keyType = 'binary') as a string. When this string value is passed to the WHERE IN clause, it fails to match the binary column in the related table.

Steps To Reproduce

Steps to Reproduce

1. Create two models with a HasMany relationship using binary keys:

   // Property.php
   use Illuminate\Database\Eloquent\Model;
   
   class Property extends Model
   {
       protected $keyType = 'binary';
       public $incrementing = false;
   
       public function blocks()
       {
           return $this->hasMany(Block::class, 'property_id', 'property_id');
       }
   }
   
   // Block.php
   use Illuminate\Database\Eloquent\Model;
   
   class Block extends Model
   {
       protected $keyType = 'binary';
       public $incrementing = false;
   }

2. Set up the database with binary columns:

   CREATE TABLE properties (
       property_id BINARY(16) PRIMARY KEY,
       -- other columns
   );
   
   CREATE TABLE blocks (
       id BINARY(16) PRIMARY KEY,
       property_id BINARY(16),
       FOREIGN KEY (property_id) REFERENCES properties(property_id)
   );

3. Insert sample data (e.g., using a ULID library to generate binary values):

   $propertyId = \ulid('01JNCAQ3VZEK2ANMN38ZMWBY9V'); // Binary ULID
   Property::create(['property_id' => $propertyId]);
   Block::create(['id' => \ulid('01JNCAQ3VZEK2ANMN38ZMWBY9W'), 'property_id' => $propertyId]);

4. Run an eager-loaded query:

   use Illuminate\Support\Facades\DB;
   
   DB::enableQueryLog();
   $property = Property::with('blocks')
       ->where('property_id', $propertyId)
       ->first();
   dd($property->blocks, DB::getQueryLog());

Expected Behavior

• The blocks relationship should load correctly, returning the related Block records.
• The relationship query should use the binary representation of property_id in the WHERE IN clause to match the binary column in the blocks table.

Actual Behavior

• The blocks relationship returns an empty collection.
• The query log shows the relationship query using a string ULID in the WHERE IN clause, which doesn’t match the binary property_id column:

  [
      [
          "query" => "select * from `blocks` where `blocks`.`property_id` in (?)",
          "bindings" => ["01JNCAQ3VZEK2ANMN38ZMWBY9V"], // String ULID
          "time" => 0.69
      ],
      [
          "query" => "select * from `properties` where `property_id` = ? limit 1",
          "bindings" => ["\x01\xXX\x34..."], // Binary ULID
          "time" => 1.4
      ]
  ]

• Direct queries with binary values work fine:

  Block::where('property_id', $propertyId)->first(); // Returns the record

Additional Context

• Laravel Version: [Insert your Laravel version here, e.g., 10.x]
• PHP Version: [Insert your PHP version here, e.g., 8.2]
• Database: MySQL (though this issue may affect other databases with binary columns)
• The issue persists even when using a closure with ::with:

  $property = Property::with(['blocks' => fn($query) => $query->where('property_id', $propertyId)])
      ->where('property_id', $propertyId)
      ->first();

  This still generates a query with both IN (string) and WHERE (binary) conditions, causing no matches:

  "select * from `blocks` where `blocks`.`property_id` in ('01JNCAQ3VZEK2ANMN38ZMWBY9V') and `property_id` = [binary_value]"

Possible Cause

The issue likely stems from HasMany::getEagerModelKeys, which retrieves the parent model’s property_id as a string rather than preserving its binary form, despite keyType = 'binary'. This string value is then passed to whereIn, leading to a type mismatch with the binary column.

Suggested Fix

Modify HasMany::addEagerConstraints to respect the keyType of the parent model and ensure binary keys are preserved. For example:

// Illuminate\Database\Eloquent\Relations\HasMany.php
public function addEagerConstraints(array $models)
{
    $keys = $this->getEagerModelKeys($models);

    if ($this->parent->getKeyType() === 'binary') {
        $keys = array_map(function ($key) {
            return $key; // Ensure binary format is preserved (e.g., bypass string casting)
        }, $keys);
    }

    if (!empty($keys)) {
        $this->query->whereIn($this->foreignKey, $keys);
    }
}

Alternatively, provide a way to disable or customize the automatic WHERE IN constraint when a closure is provided, allowing full control over the relationship query.

Workaround

As a temporary workaround, I’ve resorted to manually loading the relationship:

$property = Property::where('property_id', $propertyId)->first();
if ($property) {
    $property->setRelation('blocks', Block::where('property_id', $propertyId)->get());
}

However, this bypasses Eloquent’s eager loading benefits and isn’t ideal for larger applications.

Additional Notes

This issue may affect other relationship types (e.g., BelongsToMany) with binary keys. I’d be happy to assist further in debugging or providing a PR if needed.

[GokhanKR]

Additional Comment for GitHub Issue

After further investigation, I discovered that the root cause of this issue is related to Eloquent's casting mechanism. In my case, the property_id column had a custom cast (e.g., protected $casts = ['property_id' => 'custom:BinaryToUlidString']), which converts the binary value to a string ULID when accessed. This casted string value is then picked up by HasMany::getEagerModelKeys, leading to a mismatch in the WHERE IN clause against the binary column in the database.

As a temporary workaround, I extended the HasMany class and overrode getEagerModelKeys to use $model->getRawOriginal($this->localKey) instead, fetching the raw binary value before casting. Here’s the solution I implemented:

namespace App\Relations;

use Illuminate\Database\Eloquent\Relations\HasMany;

class BinaryHasMany extends HasMany
{
    public function addEagerConstraints(array $models)
    {
        $keys = $this->getEagerModelKeys($models);

        if ($this->parent->getKeyType() === 'binary') {
            $keys = array_map(function ($key) {
                return $key; // Already binary from getRawOriginal
            }, $keys);
        }

        $whereIn = $this->whereInMethod($this->parent, $this->localKey);

        if (!empty($keys)) {
            $this->whereInEager($whereIn, $this->foreignKey, $keys, $this->getRelationQuery());
        }
    }

    protected function getEagerModelKeys(array $models)
    {
        $keys = [];
        foreach ($models as $model) {
            if (!is_null($value = $model->getRawOriginal($this->localKey))) {
                $keys[] = $value; // Fetch raw binary value before casting
            }
        }
        return array_values(array_unique($keys));
    }
}

This resolves the issue by bypassing the cast, but it’s a workaround rather than a proper fix. Ideally, Eloquent should handle binary keys natively in HasMany’s eager loading, respecting keyType = 'binary' without requiring custom overrides. Looking forward to a more permanent solution in the core framework!

[rodrigopedra]

I understand your surprise, but on the other hand, there is nowhere in the docs which says $keyType = 'binary'; is a supported value.

I also couldn't find any code which handle this $keyType value.

So I guess this is, unfortunately, an unsupported case.

My suggestion would be to send a PR - or create a 3rd-party package - to handle this use-case. Covering not just the HasMany case, but any other cases where this $keyType value might be considered.