Eloquent Relation ignores its NULL column

[emadha]

• Laravel Version: 5.8.#
• PHP Version: 7.#.#
• Database Driver & Version: MariaDB

Description:

Eloquent with a relation, say HasMany, doesn't care about the NULL column and keep trying to get the Model.

Steps To Reproduce:

php artisan make:model Category -mc
Migaration should have something like category_id // like a parent category

Schema::create('categories', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('category_id')->nullable(); // <- parent category
            $table->string('title');
            ...
        });

Model App\Category:

/**
  * Get the parent category
  *
  * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
  */
 public function category()
 {
     return $this->belongsTo(Category::class);
 }

Until now it's fine, But:

Add the $with = ['category'] to the Eloquent and you'll get a query like this when the category_id is NULL:

Debugbar final query:
select * from `categories` where 0 = 1

App\Category->category()->toSql() Builder query:
select * from `categories` where `categories`.`id` is null

And you can get the same result if you get the relation of a null colum without using the $with:
$CategoryWithCategoryIDNULL = Category->category()->toSql();

remember The table's name is category and it has ID and another column called category_id,
you can name it parent_id if you got confused with double the names.

[staudenmeir]

Debugbar final query:
select * from categories where 0 = 1

This eager loading query is indeed unnecessary, but it would require bigger changes in the Laravel code to prevent such a query.

App\Category->category()->toSql() Builder query:
select * from categories where categories.id is null

This query does not actually get executed during lazy loading. If you run (new Category)->category, you'll see that Laravel detects the empty foreign key and doesn't execute any query (#26992).

[emadha]

@staudenmeir correct, i just checked

dump(Category::find(1)->category);

in Debugbar it doesn't log anything.

And as you said

dump(Category::with(['category'])->find(1));

will log select * from `categories` where 0 = 1

[driesvints]

@staudenmeir do you think the changes are worth solving this?

[staudenmeir]

@driesvints Although the required changes would be smaller than I initially thought, I'm leaning towards keeping the current behavior.

Compared to the unnecessary lazy loading queries from #26992, this issue only affects BelongsTo/MorphTo relationships and only eager loading queries where all foreign keys are null.

[emadha]

In my situation this generated 1 of that query of 500mirosecond each because my Eloquent (Article) requires Category, and Category needs to generate it's relative link from the parent category, so it checks for those, for performance reasons i had to load the view like that Article::with([....]), each one of those eventually has root category which has no parent category, resulting in that unneeded query.

So if you say i have 50 Articles being loaded, that's 50 0=1 queries.

I tried to solve the problem my self, updating the Builder.php but it always created a bug somewhere else in the code so i stopped for now.

I think it's worth taking a look at.

[emadha]

And if it's not a hard fix, it's better if Laravel doesn't send invalid queries

[staudenmeir]

I'll work on a draft to put it up for discussion.

The fix isn't complicated, but it adds an overhead for all relationships, not just BelongsTo/MorphTo.

[emadha]

Thank you, when i have time i'll take a look on how that thing works in code, or if you can give a hint i'll be thankful.

[driesvints]

@staudenmeir have you had time yet to look into a draft for this?

[staudenmeir]

@driesvints No, I haven't.

[driesvints]

No worries 👍

[driesvints]

@staudenmeir is there any chance you can point me in the right direction so I can maybe tackle this myself?