Eloquent with() does not always load related models when using left join on a morphOne relationship

[csandreas1]

Laravel Version

11.30.0

PHP Version

8.3.13

Database Driver & Version

1:11.5.2+maria~ubu2404
Image version: 11.5.2

Environment details:

"php": "^8.3",
"inertiajs/inertia-laravel": "^1.0",
"laravel/framework": "^v11.30.0",
"laravel/sanctum": "^4.0",
"laravel/tinker": "^2.9",
"tightenco/ziggy": "^2.0"

Description

I'm working with Laravel Eloquent and attempting to load related data using with() alongside a left join, but I'm running into issues. Specifically, I have a Company model with a morphOne relationship to a Featured model and a hasMany (or morphMany) relationship to Social models. Here are my relevant methods:

Steps To Reproduce

Create a Company model with a morphOne relationship to a Featured model and a hasMany relationship to a Social model.

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\MorphOne;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Company extends Model
{
    public function featured(): MorphOne
    {
        return $this->morphOne(Featured::class, 'featureable');
    }

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

    public function scopeJoinWithActiveFeatured($query)
    {
        return $query->leftJoin('featureds', function ($join) {
            $join->on('featureds.featureable_id', '=', 'companies.id')
                 ->whereNotNull('featureds.featureable_id')
                 ->whereDate('featureds.featured_from', '<=', now())
                 ->whereDate('featureds.featured_until', '>=', now());
        });
    }
}

Create the Featured and Social models:

class Featured extends Model
{

    use HasFactory;

    protected $guarded = ['id'];

    public function featureable(): MorphTo
    {
        return $this->morphTo();
    }
}

class CompanySocial extends Model
{
    use HasFactory;

    protected $guarded = ['id'];

    protected $visible = [
        'social_type',
        'social_url',
    ];

    public function company(): BelongsTo
    {
        return $this->belongsTo(Company::class);
    }
}

Migrations:

    public function up(): void
    {
        Schema::create('companies', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->string('name_gr')->nullable()->unique();
            $table->string('slug')->unique();
            $table->string('address');
            $table->string('email');
            $table->string('phone')->nullable();
            $table->string('website')->nullable();
            $table->string('headline')->nullable();
            $table->text('description')->nullable();
            $table->string('image_url')->nullable();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('company_socials', function(Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')
                  ->constrained()
                  ->cascadeOnDelete();
            $table->enum('social_type',
                ['x', 'facebook', 'instagram', 'linkedin', 'tiktok', 'youtube']);
            $table->string('social_url', 550);
            $table->timestamps();
            $table->unique(['company_id', 'social_type']);
        });

        Schema::create('featureds', function(Blueprint $table) {
            $table->id();
            $table->morphs('featureable');
            $table->dateTime('featured_from');
            $table->dateTime('featured_until');
            $table->timestamps();
        });
 }

Seed database:

Make sure that each company has at least one social, and make 2-3 companies featured.

Run the following query:

$companies = Company::query()
->JoinWithActiveFeatured() // Joins the featured table with conditions
->with(['socials'])
->orderBy('featureds.featureable_id', 'desc')
->orderBy('companies.name')
->get();

Observe the result:

Notice that the socials relationship does not load for the Company models when JoinWithActiveFeatured is used unless the company is featured..
If JoinWithActiveFeatured is removed, the socials relationship loads always as expected.

Some screenshots to make the problem clearer:
Notice that only 1 company is featured:

Notice that all companies have socials:

Debug bar queries:

select * from companies left join featureds on featureds.featureable_id = companies.id and featureds.featureable_id is not null and date(featureds.featured_from) <= '2024-11-10' and date(featureds.featured_until) >= '2024-11-10' where companies.deleted_at is null order by featureds.featureable_id desc, companies.name asc limit 6 offset 0
890μs
laravel
CompanyController.php#35
select * from company_socials where company_socials.company_id in (0, 1)

UI:

Complete code in controller (for clarity):

public function index(SearchListingRequest $request): Response|RedirectResponse
{
    $companies = Company::query()
                        ->when($request->filled('search'),
                            fn($query) => $query->where('name', 'like', '%'.$request->input('search').'%'))
                        ->when($request->filled('category'), fn($query) => $query->whereHas('categories',
                            fn($q) => $q->where('slug', $request->input('category'))))
                        ->when($request->filled('city'),
                            fn($query) => $query->whereHas('city',
                                fn($q) => $q->where('slug', $request->input('city'))))
                        ->leftJoinWithActiveFeatured()
                        ->with([
                            'socials',
                        ])
                        ->orderBy('featureds.featureable_id', 'desc') // Sort companies by featured status
                        ->orderBy('companies.name');

    return Inertia::render('Company/Index', [
        'companies' => $companies
            ->paginate(6)
            ->withQueryString(),
    ]);
}

[macropay-solutions]

Hi.
Can you please refactor your code and tell us if it solves your problem?

Please define the featureds relation in Company model (see https://laravel.com/docs/11.x/eloquent-relationships#inline-relationship-existence-queries)

public function index(SearchListingRequest $request): Response|RedirectResponse
{
    $companies = Company::query()
                        ->when($request->filled('search'),
                            fn($query) => $query->where('name', 'like', '%'.$request->input('search').'%'))
                        ->when($request->filled('category'), fn($query) => $query->whereHas('categories',
                            fn($q) => $q->where('slug', $request->input('category'))))
                        ->when($request->filled('city'),
                            fn($query) => $query->whereHas('city',
                                fn($q) => $q->where('slug', $request->input('city'))))
                        ->whereRelation('featureds', function ($query) { // or whereMorphRelation
                               $query->whereNotNull('featureable_id')
                               ->whereDate('featured_from', '<=', now())
                               ->whereDate('featured_until', '>=', now());
                        })
                        ->with([
                            'socials',
                        ])
                        ->orderBy('featureds.featureable_id', 'desc') // Sort companies by featured status
                        ->orderBy('companies.name');

    return Inertia::render('Company/Index', [
        'companies' => $companies
            ->paginate(6)
            ->withQueryString(),
    ]);
}

Have a look at this issue
#51825 (comment)
the with uses no constraints when eager loading relations.
Update.
Has nothing to do with the above linked issue.

[csandreas1]

@macropay-solutions

The featured relationship is correctly defined in the Company model, as shown in the description.

However, using whereRelation with this MorphOne relationship does not produce a LEFT JOIN as the original query does. Instead, it generates a WHERE EXISTS clause. This behavior can lead to incorrect results.

In this case, I’m not only looking to filter results based on the existence of featured companies; I need to join the featureds table directly with companies since this is a one-to-one relationship and also eager load company socials.

The problem is that not all socials are eager loaded but Only socials associated with featured companies.

Additionally, whereMorphRelation appears to be incompatible with MorphOne relationships, causing an error in this scenario.

[macropay-solutions]

Thank you.
featureds.id overlaps with companies.id after the join. Maybe that is the issue. Null overlapping int.

edit

select companies.*, featureds.featurable_type, featureds.id as featureds_id,
 featureds.featured_from, featureds.featured_until, featureds.featureable_id 
from companies left join featureds on featureds.featureable_id = companies.id 
and featureds.featureable_id is not null and date(featureds.featured_from) <= '2024-11-10' 
and date(featureds.featured_until) >= '2024-11-10'
 where companies.deleted_at is null 
order by featureds.featureable_id desc, companies.name asc limit 6 offset 0

[csandreas1]

Thank you. Adding a select solved the issue

     $companies = Company::query()
                            ->select('companies.*', 'featureds.featureable_id')
                            ->when($request->filled('search'),
                fn($query) => $query->where('name', 'like', '%'.$request->input('search').'%'))
                            ->when($request->filled('category'), fn($query) => $query->whereHas('categories',
                                fn($q) => $q->where('slug', $request->input('category'))))
                            ->when($request->filled('city'),
                                fn($query) => $query->whereHas('city',
                                    fn($q) => $q->where('slug', $request->input('city'))))
                            ->leftJoin('featureds', function($join) {
                                $join->on('featureds.featureable_id', '=', 'companies.id')
                                     ->whereNotNull('featureds.featureable_id')
                                     ->whereDate('featureds.featured_from', '<=', now())
                                     ->whereDate('featureds.featured_until', '>=', now());
                            })
                            ->with([
                                'socials',
                            ])
                            ->orderBy('featureds.featureable_id', 'DESC')
                            ->orderBy('companies.name');

[macropay-solutions]

Please convert this issue into a discussion then and mark the solution. @csandreas1