Framework: [5.4] whereHas on polymorphic relations not work

[lkmadushan]

• Laravel Version: 5.4
• PHP Version: 7.1
• Database Driver & Version: PostgreSQL 9

Description:

when try whereHas method on a polymorphic relation does not works as expected.

Statistics::whereHas('applicable', function ($query) {
        $query->where('employee.id', 1);
    })->toSql();

select * from "statistics" where exists (select * from "statistics" as "laravel_reserved_0" where "laravel_reserved_0"."id" = "laravel_reserved_0"."applicable_id" and "employee"."id" = ?)

[Dylan-DPC]

@lkmadushan what's the output? any error? or does it give an empty response?

[themsaid]

Please provide more details, doesn't work is not a bug report.

[lkmadushan]

Sorry. I have updated the description. Currently whereHas on polymorphic relations doesn't add constraints to the relationship query. It always check existence in same table.

[themsaid]

This won't be an easy fix afaik, we'll need to run several queries on several related tables in order for this work.

[paulofreitas]

This is related to #5429. There are some workarounds discussed there, like this one: https://github.com/laravel/framework/issues/5429#issuecomment-268178279

It's a known limitation that won't probably be easy to fix. Workarounds indeed work but they involve some tricky engineering.

[aios]

Marked as No-fix when polimorph relations has not intersect collumns

[bunnypro]

i'm currently using this code in my own project

my Model (\App\Production\Models\Production Model)

public function detail()
{
    return $this->morphTo('detail');
}

public function scopeDetailable($query, $callable = null)
{
    list($type, $key) = $this->getPolymorphicFields();

    $this->newQuery()->distinct()->get([$type])->keyBy($type)->map(function ($model)  use ($type) {
        return (new $model->{$type})->getTable();
    })->each(function ($table, $modelClass) use (&$query, $key, $callable) {
        $model = new $modelClass;

        $query = $query->orWhereExists(function ($query) use ($table, $model, $key, $callable) {
            $query->select('*')->from($table)->whereRaw("{$this->getTable()}.{$key} = {$table}.{$model->getKeyName()}")
                ->when($callable instanceof \Closure, $callable);
        });
    });

    return $query;
}

protected function getPolymorphicFields()
{
    $relation = $this->detail();

    return [$relation->getMorphType(), $relation->getForeignKey()];
}

my Controller

\App\Production\Models\Production::detailable(function ($query) {
    $query->whereDate('panen_future_date', '2017-02-08');
})->with('detail')->get()

and it works for my use case.

please note that all model related to \App\Production\Models\Production model have 'panen_future_date' filed in its table.

[Dylan-DPC]

@bunnypro can you try it on a new project with just the STR (steps to reproduce), it will be better :)

[bunnypro]

i forgot to compare the _type filed.

ok i'll try to repoduce it with fresh laravel project and also comparing the _type filed.

[bunnypro]

i have reproduced in a fresh laravel project. here is the https://github.com/bunnypro/querying-polymorphic

just migrate and use DummySeeder class for seeding.
i didn't write any unit testing, but i wrote a route in web route (web.php) for testing, just serve and access
/?search=your search

[Dylan-DPC]

Do you get the same issue or not?

[bunnypro]

yes i do
that's just my temporary solution for this issue.

[vesteves]

Ppl,
I solve it with strange way but it's working... I'll try explain:

code:

$agendamentos = Agendamento::where(
            'administrador_id',
            Auth::user()->userable_id)->orderBy('id', 'desc')
        ->get();

and It's the code to return object to view:

return view('agendamentos.lista', [
            'agendamentos' => $agendamentos,
            'clientes' => $clientes
        ]);

The hint and the strange thing on the same is:

$clientes = Cliente::whereHas('credito',
    function ($query) {
        $query->where('administrador_id', Auth::user()->userable_id);
    })
->get();
foreach ($agendamentos as $agendamento) {
    foreach ($agendamento->cliente as $cliente) {
        $agendamento->cliente->users = $agendamento->cliente->users;
    }
}
return view('agendamentos.lista', [
    'agendamentos' => $agendamentos
]);

If I do this loop, for NOTHING, it work well and i can use on blade it:

@foreach ($agendamentos as $agendamento)
    {{ $agendamento->cliente->users->name }}
@endforeach

and it on vue:

<ul>
<li v-for="agendamentos in agendamento">{{ agendamento.cliente.users.name }}

I hope it help

[phroggyy]

@themsaid I've thought of a semi-solution after discussing this with another developer. It does have a fairly big caveat, but I still think it's better than nothing... Basically:

Prerequisite:
The user must use morphMap _at least_ for the target model (the relation we want to query our whereHas on).

Solution:
If the Relation::morphMap() is indexed, that is, has the following structure:

[
    User::class,
    Post::class,
    Comment::class,
]

We know that the {morphable}_type is the _name of the related table_. As such, the subquery performed by laravel should be able to use that value straight away for the select.

If the Relation::morphMap() is not indexed, that is, has a structure like:

[
    'user' => User::class,
    'post' => Post::class,
    'comment' => Comment::class,
]

We cannot be sure what the table name is. As such, the solution is... Ugly. We can loop over the morph map, running (new $model)->getTable() on each of them. After that, construct a SQL SWITCH statement that runs on the {morphable}_type column. That's pretty ugly, but it was the only solution I could think of that'd solve it for both scenarios (as long as morph map is present).

[morningmemo]

@bunnypro your solution is good.
but it is do not support for whereHas in polymorphic whereHas.
so i send to 'pull request' that solution for this problem to that repository.

[laurencei]

@staudenmeir - does your merged PR fix this issue? Can I close it?

[staudenmeir]

@laurencei The issue itself is basically unfixable, my PR just added a proper exception. I think this can be closed.

[staudenmeir]

Laravel 5.8.27 adds whereHasMorph(): #28928