Laravel Eloquent ORM - Mismatch between generated SQL and querybuilder results when selecting all models where two relationships are both absent

user_6111743 0
Viewed 141

Let's say I have a model Foobar with relationships named baz and quux. Foobar belongsTo Baz and Baz hasOne Foobar. This means there is a foreign key column in the Foobar table for baz_id. The relationships are defined correctly in the models. In case it is relevant, this model caching library is used on both models: https://github.com/GeneaLabs/laravel-model-caching

I would like to query all Foobar that do not have either relationship; meaning I only want to select Foobar where BOTH baz and quux relationships are absent. I do so as follows:

Foobar::doesntHave('baz', 'AND')->doesntHave('quux')->get();

Now let's say $foobar is a Foobar instance with no relationships, and $baz is a freshly created Baz instance. I associate the two like so:

$baz->foobar()->associate($foobar);
$baz->save();

Now, I run the above query again. The row represented by $foobar is still appearing in the results, even though it shouldn't since it now has a non-null baz relationship. My question is: why is this happening, and how can I fix it?

I played around in artisan console while debugging this. In the same artisan session:

Foobar::doesntHave('baz', 'AND')->doesntHave('quux')->get();
// This gets a collection that is not empty, the first item is a Foobar instance that definitely has a baz

Foobar::doesntHave('baz', 'AND')->doesntHave('quux')->first();
// This is null (?)

Foobar::doesntHave('baz', 'AND')->doesntHave('quux')->count();
// This is 0 (?)

Foobar::doesntHave('baz', 'AND')->doesntHave('quux')->get()->get(0)->baz;
// This gets the Baz object attached to the first instance

To double-check that the SQL is correct:

Foobar::doesntHave('baz', 'AND')->doesntHave('quux')->get()->toSql();
// This outputs the generated raw SQL.

I input the raw SQL into a SQL client and got the correct result.

I also attempted / checked while debugging:

  1. $foobar->touch() - did not change results.
  2. Made sure API endpoint was not cached by browser, Cloudflare, or anything else
eloquent laravel orm sql php
2 Answers

You might want to try this:

Foobar::whereDoesntHave('baz')->whereDoesntHave('guux')->get();
user_29949200

I discovered the issue originated in the model caching library used on these models: https://github.com/GeneaLabs/laravel-model-caching

When I ran the library's command for completely invalidating Foobar cache, it resolved the issue. It looks like the library does not correctly invalidate cache in this scenario, though I can't explain why without digging deeper into the library. Their README indicates this may be a known issue.

php artisan modelCache:clear --model='App\Models\Foobar'

To work around it, a few options came to mind:

  1. Programmatically run the artisan command for clearing model cache whenever the relationship is updated.
  2. Rewrite query to bypass model caching.
  3. Look for different model caching library.

We opted to remove model caching from these models entirely; we were able to do so because it was an "easy" premature optimization, but may not be necessary. We reasoned that we will remove the premature optimization for now and worry about it again only if it causes problems.

user_61117430
Related