Eager Loading One-to-Many Relationship in Laravel Returns Empty Collection Despite Data Being Present

[bridgeyuwa]

Laravel Version

11.14.0

PHP Version

8.2.12

Database Driver & Version

MySQL 8.0 on XAMPP

Description

I'm facing an issue with eager loading in Laravel where a one-to-many relationship returns an empty collection, even though there is data present in the database. The same relationship works correctly when accessed using lazy loading.

I have two models, Institution and Social, where an Institution has many Socials. Despite there being related records in the socials table, eager loading of the socials relationship returns an empty collection. However, the same relationship returns data when accessed lazily.

The $institution->socials relationship works as expected when accessed using lazy loading (e.g., $institution->socials outside of load).
The issue occurs specifically with eager loading.

Steps To Reproduce

Social Model:

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Social extends Model
{
    use HasFactory;

    public function institution()
    {
        return $this->belongsTo(Institution::class);
    }
}

Institution Model:

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Institution extends Model
{
    use HasFactory;

    public function socials()
    {
        return $this->hasMany(Social::class);
    }
}

Controller Code:

public function show(Institution $institution)
{
    $allInstitutions = Institution::whereNotNull('rank')
                                  ->where('category_id', $institution->category->id)
                                  ->orderBy('rank')
                                  ->get();

    $institution->load([
        'schooltype',
        'category.institutions',
        'term',
        'catchments',
        'state.institutions',
        'state.region.institutions',
        'socials',
        'levels.programs' => function($query) use($institution) {
            $query->wherePivot('institution_id', $institution->id);
        }
    ]);

    dd($institution->socials); // Returns empty collection
}

Database Schema

--Institutions Table

CREATE TABLE institutions (
    id VARCHAR(255) PRIMARY KEY,
    -- other columns
);

--Socials Table

CREATE TABLE socials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    institution_id VARCHAR(255),
    -- other columns
    FOREIGN KEY (institution_id) REFERENCES institutions(id)
);

Query Log: Verified that the SQL query for loading socials executes correctly, but the collection returned is empty.

dd(DB::getQueryLog());

Direct Query: Manually querying the socials table shows that records with the correct institution_id are present.

$socials = Social::where('institution_id', $institution->id)->get();
dd($socials);

Cleared Caches: Used php artisan cache:clear, php artisan config:clear, and other cache clearing commands.
Data Integrity: Checked that the foreign key relationship is set up correctly and that the data in the socials table matches the institution_id.

php artisan cache:clear
php artisan config:clear
php artisan route:clear
php artisan view:clear

Testing in Isolation: Tested the eager loading in isolation to see if other parts of the code are affecting the results:

$institution = Institution::find($id);
$institution->load('socials');
dd($institution->socials);

Using
Laravel 11,
XAMPP 8.2 (PHP 8.2, Mysql 8.0)

Thanks

[bulletproof-coding]

Can you please listen to QueryExecuted event and print all the executed queries?

use

Log::info($queryExecuted->connection
->query()
->getGrammar()
->substituteBindingsIntoRawSql($queryExecuted->sql, $queryExecuted->connection->prepareBindings($this->bindings)));

Update: Sorry. I missed the fact that you checked the queries and that they are ok.

Would be a good idea if you would define all your relations with all params and not let laravel guess them for you.

[bulletproof-coding]

Also, instead of using load I would use ->with() on the query builder.

[bridgeyuwa]

Also, instead of using load I would use ->with() on the query builder.

Thank you for your suggestion. However, I believe using ->with() is not applicable in this scenario because it involves a single instance of Institution. The ->load() method is appropriate for eager loading relationships on an existing model instance.

I have now explicitly defined the relationship parameters between the two models to ensure there is no ambiguity. However, the issue persists even with the explicit definitions.

Institution Model

public function socials()
{
    return $this->hasMany(Social::class, 'institution_id', 'id');
}

Social Model

public function institution()
{
    return $this->belongsTo(Institution::class, 'institution_id', 'id');
}

[crynobone]

Hey there, thanks for reporting this issue.

We'll need more info and/or code to debug this further. Can you please create a repository with the command below, commit the code that reproduces the issue as one separate commit on the main/master branch and share the repository here?

Please make sure that you have the latest version of the Laravel installer in order to run this command. Please also make sure you have both Git & the GitHub CLI tool properly set up.

laravel new bug-report --github="--public"

Do not amend and create a separate commit with your custom changes. After you've posted the repository, we'll try to reproduce the issue.

Thanks!

[staudenmeir]

@bridgeyuwa Did you set the required properties for string primary keys?

class Social extends Model
{
    public $incrementing = false;

    protected $keyType = 'string';
}

[bulletproof-coding]

@bridgeyuwa Did you set the required properties for string primary keys?

class Social extends Model
{
    public $incrementing = false;

    protected $keyType = 'string';
}

I remember this was an issue on another thread. I missed the fact that the id is a varchar. Great spotting.

[bridgeyuwa]

Hey there, thanks for reporting this issue.

We'll need more info and/or code to debug this further. Can you please create a repository with the command below, commit the code that reproduces the issue as one separate commit on the main/master branch and share the repository here?

Please make sure that you have the latest version of the Laravel installer in order to run this command. Please also make sure you have both Git & the GitHub CLI tool properly set up.

laravel new bug-report --github="--public"

Do not amend and create a separate commit with your custom changes. After you've posted the repository, we'll try to reproduce the issue.

Thanks!

Thank you for your guidance. I have created a new repository as requested with the minimal code needed to reproduce the issue here https://github.com/bridgeyuwa/bug-report .

Please let me know if you need any more information.

Thanks.

[bridgeyuwa]

@bridgeyuwa Did you set the required properties for string primary keys?

class Social extends Model
{
    public $incrementing = false;

    protected $keyType = 'string';
}

@bridgeyuwa Did you set the required properties for string primary keys?

class Social extends Model
{
    public $incrementing = false;

    protected $keyType = 'string';
}

I remember this was an issue on another thread. I missed the fact that the id is a varchar. Great spotting.

The relationship in question has nothing to do with the primary key on the Socials model.

In the direction of the relationship, only the foreign key institution_id is important.

[bulletproof-coding]

@bridgeyuwa I had this issue before with the primary key not being int and the relations would not load. See also this #51135 (comment)

Your institution id being string, if you put it int it will not load the relations. Anyway you confirmed this is not the case, so it must be something in laravel 11. I never had this issue in 8 9 10. If it works in 10 for example then it is clearly a l11 bug.

UPDATE
Tested in laravel 10

    echo (Institution::query()->first()->load('socials')->toJson());
    die;

or

     echo (Institution::query()->with('socials')->first()->toJson());
    die;

{
    "id": "ffgg",
    "slug": "ddf",
    "name": "fff",
    "former_name": null,
    "abbr": null,
    "description": null,
    "established": null,
    "state_id": 1,
    "lga_id": 1,
    "locality": null,
    "schooltype_id": 1,
    "category_id": 1,
    "term_id": 1,
    "address": null,
    "longitude": null,
    "latitude": null,
    "website": null,
    "email": null,
    "rank": null,
    "created_at": "2024-01-01T00:00:00.000000Z",
    "updated_at": null,
    "socials": [
        {
            "socialtype_id": 1,
            "institution_id": "ffgg",
            "url": "https:\/\/d.com"
        },
        {
            "socialtype_id": 2,
            "institution_id": "ffgg",
            "url": "https:\/\/d.comm"
        }
    ]
}

[bridgeyuwa]

@bridgeyuwa I had this issue before with the primary key not being int and the relations would not load. See also this #51135 (comment)

Your institution id being string, if you put it int it will not load the relations. Anyway you confirmed this is not the case, so it must be something in laravel 11. I never had this issue in 8 9 10. If it works in 10 for example then it is clearly a l11 bug.

UPDATE Tested in laravel 10

    echo (Institution::query()->first()->load('socials')->toJson());
    die;

or

     echo (Institution::query()->with('socials')->first()->toJson());
    die;

{
    "id": "ffgg",
    "slug": "ddf",
    "name": "fff",
    "former_name": null,
    "abbr": null,
    "description": null,
    "established": null,
    "state_id": 1,
    "lga_id": 1,
    "locality": null,
    "schooltype_id": 1,
    "category_id": 1,
    "term_id": 1,
    "address": null,
    "longitude": null,
    "latitude": null,
    "website": null,
    "email": null,
    "rank": null,
    "created_at": "2024-01-01T00:00:00.000000Z",
    "updated_at": null,
    "socials": [
        {
            "socialtype_id": 1,
            "institution_id": "ffgg",
            "url": "https:\/\/d.com"
        },
        {
            "socialtype_id": 2,
            "institution_id": "ffgg",
            "url": "https:\/\/d.comm"
        }
    ]
}

Can you also try the same on Laravel 11 to confirm?

[bulletproof-coding]

I don't have laravel 11 locally. Until 12 is out, I'll not set 11 locally for stability reasons.

[ImSidow]

n't have laravel 11 locally.

I understand your concerns about stability. However, being an early adopter of Laravel 11 can be quite beneficial. The codebase in Laravel 11 introduces some significant changes compared to previous versions. Getting familiar with these updates early on can help ease the transition to future versions, such as Laravel 12, and give you a head start in adapting your projects

[bulletproof-coding]

@ImSidow I seriously think of never using laravel >=11 unless forced...

[lrljoe]

You've given an example of loading this, which seems to have nested "institution" relations.

    $institution->load([
        'schooltype',
        'category.institutions',
        'term',
        'catchments',
        'state.institutions',
        'state.region.institutions',
        'socials',
        'levels.programs' => function($query) use($institution) {
            $query->wherePivot('institution_id', $institution->id);
        }
    ]);

What happens if you just load the "socials" relation, or preferably use the "with" approach when building your initial query?
Are you certain that the "id" you're passing in actually has a Social associated with it?

[bridgeyuwa]

You've given an example of loading this, which seems to have nested "institution" relations.

    $institution->load([
        'schooltype',
        'category.institutions',
        'term',
        'catchments',
        'state.institutions',
        'state.region.institutions',
        'socials',
        'levels.programs' => function($query) use($institution) {
            $query->wherePivot('institution_id', $institution->id);
        }
    ]);

What happens if you just load the "socials" relation, or preferably use the "with" approach when building your initial query? Are you certain that the "id" you're passing in actually has a Social associated with it?

All these steps have clearly been mentioned in the Original Post. also if you followed the discussion, you will see that ->with() cannot be used on a single instance.

[lrljoe]

Maybe I'm misunderstanding you, but with() can absolutely be used on a single instance.

E.g.

User::where('email', '[email protected]')->with('teams')->first()

Would return the User model "Joe", and any "Teams" relations.

User::where('email', '[email protected]')->with(['teams', 'teams.location'])->first()

Would return the "locations" relationship on the "teams" relationship as well.

I'm genuinely trying to replicate the issue you're presenting.
I've got a combination of UUID (Char 36), ULID, and BigInt primary keys, with a mixture of relations, but I haven't managed to break it in the same way that you're demonstrating.

[bridgeyuwa]

Maybe I'm misunderstanding you, but with() can absolutely be used on a single instance.

E.g.

User::where('email', '[email protected]')->with('teams')->first()

Would return the User model "Joe", and any "Teams" relations.

User::where('email', '[email protected]')->with(['teams', 'teams.location'])->first()

Would return the "locations" relationship on the "teams" relationship as well.

I'm genuinely trying to replicate the issue you're presenting. I've got a combination of UUID (Char 36), ULID, and BigInt primary keys, with a mixture of relations, but I haven't managed to break it in the same way that you're demonstrating.

Suppose, using your example, I have:
$user = User::where('email', '[email protected]')->first();

I cannot do this:
$user->with('teams');

Instead, I have to do this:
$user->load('teams');

Now, in a case like this:
$institution = Institution::find(1);

how do you eager load the relations on $institution?

This scenario is relevant due to model binding. With type hinting, there’s no need to manually retrieve models from the database in the controller. For example:

public function show(Institution $institution) {
    // Now an institution (whose id was passed) is automatically available.
    dd($institution->id);
}

Since a single institution has already been retrieved (this is what I meant by a single instance), the only way to eager load relationships is to use the ->load() function like this:
$institution->load('socials');

Using:
$institution->with('socials');

will be invalid.

Although your method is also a valid approach, it will still end with the same verdict: the relationship is not eager loaded. Whether using ->load() on a single instance result or ->with() using your method.

The issue persists using both methods.

Here is the link to the bug repo I created with the code to replicate the issue. https://github.com/bridgeyuwa/bug-report

[bridgeyuwa]

After much, I discovered that the problem was related to how Laravel compares the id when performing eager loading. I can't believe I missed this.

**Query log of ** $institution = Institution::with(['socials'])->find('AAU');

array:2 [▼ // app\Http\Controllers\InstitutionController.php:309
  0 => array:3 [▼
    "query" => "select * from `institutions` where `institutions`.`id` = ? limit 1"
    "bindings" => array:1 [▼
      0 => "aau"
    ]
    "time" => 2.2
  ]
  1 => array:3 [▼
    "query" => "select * from `socials` where `socials`.`institution_id` in (?)"
    "bindings" => array:1 [▼
      0 => "AAU"
    ]
    "time" => 1.46
  ]
]

My id field in the institutions table is a string, and the id values are stored in uppercase in the database. However, when Laravel performs eager loading, it compares these id values case-sensitively with a lowercase converted id, leading to a mismatch. As a result, eager loading returned an empty collection because the uppercase id values in the database did not match the lowercase id values Laravel expected during the comparison.

Solution:

To resolve the issue, I ensured that the id values in the database are stored in lowercase. By doing this, the case-sensitive comparison performed by Laravel during eager loading now correctly matches the id values, and the relationship loads as expected.

Steps Taken:

Change the id values in the database: I updated the id values in the institutions table to be stored in lowercase.

Verify the fix: After making this change, I tested the eager loading again, and it successfully loaded the related Social models without returning an empty collection.

I believe the issue is now resolved if it is intentional that Laravel expects all VARCHAR primary keys to be lowercase. if not I believe it ought to be fixed.

Thanks for the support.

[bulletproof-coding]

A question still remains, Why the lowercase cast?

[bridgeyuwa]

A question still remains, Why the lowercase cast?

That’s a question for @taylorotwell and the Laravel Team to answer.

[lrljoe]

@bridgeyuwa - Interesting find!

I know that Laravel converts column names to lowercase, but wasn't aware it was also converting the values. It makes sense given that some collations will throw a hissy fit. And makes sense from a non-case-sensitive approach with indexing etc, but interesting to know.

I tend to use UUIDs/ULIDs for the most part, as a lot of the data is re-used across dozens of instances.

[wizzymore]

I can't reproduce this issue on a properly configured table, i replaced most of the query with CONFIDENTIAL but the bindings can still be seen.

The releation is self-referencing the same table, everything works like a charm, even with the format that we use in production: "ADV000000" it still searches as "ADV000000".

> CONFIDENTIAL::with('test')->find('TESTING')
= null

> DB::getQueryLog()               
    [
      "query" => "select * from "CONFIDENTIAL" where "CONFIDENTIAL_id" = ? limit 1",
      "bindings" => [
        "TESTING",
      ],
      "time" => 1.49,
    ],
    [
      "query" => "select * from "CONFIDENTIAL" where "CONFIDENTIAL_id" in (?)",
      "bindings" => [
        "ADV1430468",
      ],
      "time" => 0.63,
    ],

This table is in production since Laravel was version 8 we went through all of the weekly updates and from version to version and never had an issue.

As a side note, the driver is Postgres, may this be a driver related issue, if it's an issue at all cause i also can't reproduce the issue in the repo provided.

[wizzymore]

This is the output from your repo.

Also, not wanting to be rude or anything, but, when providing a repo for people to test something, make sure the migrations run and there are seeders setup or a easy way to create test data so people can test the problem and they don't need to understand your business logic, only the laravel logic that's broken.

[crynobone]

Hey there,

We're closing this issue because it's inactive, already solved, old, or not relevant anymore. Feel free to open up a new issue if you're still experiencing this problem.

[bulletproof-coding]

For the record this is the answer #52201 (comment)