Framework: whereHas() not working with MorphTo()

[nkeena]

The whereHas() method for querying a polymorphic relationship doesn't seem to be working.

Here are the relationships:

Transaction.php

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

Reservation.php

public function transactions()
    {
        return $this->morphMany('Transaction', 'transactionable');
    }

Project.php

public function transactions()
    {
        return $this->morphMany('Transaction', 'transactionable');
    }

And here is what I am trying to do:

return Transaction::whereHas('transactionable', function($q){
        return $q->where('id', '=', '1');
    })->get();

Result:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'transactions.' in 'where clause' (SQL: select * from `transactions` where (select count(*) from `transactions` where `transactions`.`transactionable_id` = `transactions`.`` and `id` = 1) >= 1)

Is this possible?

[dorcari]

Did you managed to get this working? I'm having the same problem.

[gpyrbris]

Yep, having the same problem!

[carlosrochap]

same here

[sebastiaanluca]

Running into the same issue. It doesn't even seem to look at the morph type, it just does a select from the same table.

Any more information on it?

[blankhua]

I have the same problem. Very sad.

[blankhua]

I ran the code:

$tasksQuery->whereHas('taskable', function ($q) use ($search) {
    $q->where('title', 'like', "%{$search}");
});

but it throw an exception.

[sebastiaanluca]

I reaallly need this asap, so I'm going to look into it in a few days and try to debug it. I don't know anything about the code, so if anyone could clarify what I should be looking for, go ahead.

[sgelbart]

Yup same here. Wonder if this was ever working?

[wajatimur]

I think this kinda impossible currently, because if you see the MorphTo class were derived from BelongsTo relation class, but in BelongsTo model relationship declaration of the model or foreign key were explicitly configured, whereby in MorphTo relationship is not pointing to related model.

This polymorphism relationship only fully capable when the model instance/object were loaded whereby the data_type value that pointing to related model class can be obtained. And in whereHas situation, the builder class need to gather all the query related information such as filter, binding and etc to be use later on query generation and execution. It's impossible to create a queries without knowing the kind row/data type that will be retrieve later

You can have a look at file Eloquent\Relations\BelongsTo.php, method 'getRelationCountQuery' this is where everything is falling apart.

I'm sure @taylorotwell and others core developer were fully aware about this issue, but it might be too hacky to make this possible. Anyway it's just my opinion.

[GrahamCampbell]

Looks like this is a no-fix then.

[sebastiaanluca]

Err. Any workaround then?

[sgelbart]

What about throwing a better exception when it happens? Also would like to
see a work-around :-) Also, what about maybe passing a parameter that's an
array of possible table/model names?

On Thu, Feb 5, 2015 at 10:52 AM, Sebastiaan Luca [email protected]
wrote:

Err. Any workaround then?

—
Reply to this email directly or view it on GitHub
https://github.com/laravel/framework/issues/5429#issuecomment-73103451.

[wajatimur]

You could try to have the Builder class to look ahead for the value of the morphed relation class. Code provided is the only proof of concept, it's not a workaround or a suitable solution. Please do not use in your code/project.

As you can see the line with variable $look_ahead is trying to fetch($parent::first) the data/model instance and get a morphed relation of it.

illuminate/database/Illuminate/Database/Eloquent/Builder.php

    protected function getHasRelationQuery($relation_name)
    {
        return Relation::noConstraints(function() use ($relation_name)
        {
            $relation =  $this->getModel()->$relation_name();

            if( get_class($relation) == 'Illuminate\Database\Eloquent\Relations\MorphTo'){
                $parent = $relation->getParent();
                $look_ahead = $parent::first();
                $relation = $look_ahead->{$relation_name}();
            };

            return $relation;
        });
    }

By using this the whereHas execution will return a value without failing, although it might not returning the right data due to the poor look ahead mechanism. It might be a good start point to explore other possibilities.

[sebastiaanluca]

Thanks @wajatimur! By using an adjusted version of your code, I can use whereHas by providing the …able_type. It only works for a single type, but in a sense, that's only logical. Does what it needs to for me.

/**
 * Get the "has relation" base query instance.
 *
 * @param  string  $relation
 * @return \Illuminate\Database\Eloquent\Builder
 */
protected function getHasRelationQuery($relation)
{
    return Relation::noConstraints(function() use ($relation)
    {
        $name = $relation;
        $relation = $this->getModel()->$relation();

        if (get_class($relation) === 'Illuminate\Database\Eloquent\Relations\MorphTo') {
            $lookAhead = $this->getModel()->where($name . '_type', '=', 'App\Models\Auth\Users\Admin')->first();
            $relation = $lookAhead->{$name}();
        };

        return $relation;
    });
}

Feedback is appreciated.

[wajatimur]

Hi @Quagh, glad its work! A whereHas method will construct COUNT SQL query for each level of whereHas query _plus_ with WHERE query using addWhere method in Builder class if I'm were not mistaken.

By not having to scheme deeper on the Builder class code and i think the only possible solution is to place a placeholder in the generated query and it will refer back to some special method such as getHasRelationQueryMorphed to get an appropriate sub query to be inject. But anyway, its just a theory.

[sebastiaanluca]

I'm not really following when you mention to put a placeholder in the generated query. Can you elaborate on that?

Anyway, I forked the Laravel repo and applied my changes to them. If anyone's interested, check out https://github.com/Quagh/framework/tree/database/fix_whereHas_with_morphTo.

Here's an example of how to use it. You just pass a string or array of strings of types it should look for. In my opinion, that's the only workable way the query builder can gather all the necessary elements to apply the whereHas. Another way would be to query the whole table, check for all different able_types, find the models, check the fields/relationships on there, etc. But not really do-able.

 $this->whereHas('userable', function ($query) use ($id) {
    $query->where('id', '=', $id);
}, '>=', 1, ['App\Models\Contact', 'App\Models\Admin']);

Composer.json:

"require": {
    "laravel/framework": "dev-database/fix_whereHas_with_morphTo as 4.2.17"
},

"repositories": [
    {
        "type": "vcs",
        "url": "https://github.com/Quagh/framework.git"
    }
],

And this is the query it produces when executing the code above (in my app);

select * from `users` where `users`.`deleted_at` is null and ((select count(*) from `admins` where `users`.`userable_id` = `admins`.`id` and `id` = ? and `admins`.`deleted_at` is null and `users`.`userable_type` = ?) >= 1 or (select count(*) from `contacts` where `users`.`userable_id` = `contacts`.`id` and `id` = ? and `contacts`.`deleted_at` is null and `users`.`userable_type` = ?) >= 1)

As you see, it wraps a single whereHasquery in parenthesis. This to make sure where 'users'.'deleted_at' is null applies to all has queries when providing multiple able types.

Trying to run phpunit tests right now to verify it doesn't break anything else. I don't have much experience writing tests myself, so if anyone feels like it, go right ahead :)

Again, feedback / testing appreciated.

---

OK (1349 tests, 2777 assertions)

---

@wajatimur @GrahamCampbell

[alexw23]

:+1: Same here

[fnfilho]

Yep! Same here!

[blankhua]

I worked out a stupid solution:

Comment morphTo Post and Tutorial:

public function scopeCommentableExists($query)
{
    return $query->where(function ($query) {
        $query->where(function ($query) {
                $query->where('commentable_type', 'Post')
                    ->has('post');
            })
            ->orWhere(function ($query) {
                $query->where('commentable_type', 'Tutorial')
                    ->has('tutorial');
            });
    });
}

[wajatimur]

Nice try @blankhua, that's a hard-coded recursive query. Anyway can anybody confirm, does this issue still exist in Laravel 5?

[threesquared]

Still having this issue in Laravel 5

[fourstacks]

I'm also seeing the same issue in L5 (5.0.30). Would be great to have an official fix soon as the bugs been around for a while. Thanks to @Quagh for the interim solution though - I'll check that out

[GrahamCampbell]

We're open to pull requests. :)

[fourstacks]

Would love to but it's a level of voodoo slightly above my pay grade! Sorry if I sounded demanding... ; )

[sebastiaanluca]

@GrahamCampbell I think I can do a pull request, though I'd love some feedback on my implementation. It requires an extra variable (and clutters the morph class a bit), but it's the only viable solution I've come up with so far. It works though, which I think is the most important part :)

[lukasgeiter]

@Quagh Just send the PR and you'll get feedback :wink:

[cmburcus]

Are there any news for the PR? :(

[sebastiaanluca]

Haven't really had time to look into it. My code's available so if anyone wants to have a go at it… Should work out-of-the-box with some cleanup.

[pedromanoel]

@nkeena, please correct me if I'm wrong, but in your example, you are trying to find the transactions of both Reservation and Project with id = 1. Is that right? You could do the same simply by querying for
transactionable_id in your Transactions table.

If you know which model you want to search, you can use a join instead of a whereHas. Following @nkeena's example:

return Transaction::join('reservations', 'transactions.transactionable_id', '=', 'reservations.id')
    ->where('reservations.id', 1);
})->get();

I'm kind of pressed with time, but as soon as I can I'll post a use case similar to what I faced.

[Aragami23]

So, no official fix for this? :(
Now I hate polymorphic tables :P

[wajatimur]

@Aragami23 this is current limitation on this feature.

@pedromanoel not working on polypmorhic as relation between table is dynamic were determined upon query.

[dehypnosis]

not yet???? ;(
Now I hate polymorphics also

[sebastiaanluca]

I don't think there's any other solution than to pass it a list of models the query has to check for. See my example https://github.com/laravel/framework/issues/5429#issuecomment-74334956.

[dehypnosis]

@sebastiaanluca, thanks for the comment. Is that solution fit to laravel 5.1.*?

[sebastiaanluca]

@dehypnosis I suppose, but haven't tested it as I didn't need the functionality anymore.

[ghost]

As this thread was closed, but I needed polymorph support for whereHas method I have adopted @sebastiaanluca code in his pull request to be compatible with Laravel 5.2.

Here is my gist https://gist.github.com/ddctd143/03b92aed6c76986dbd99

Those two files extends laravel default builder, can be used with model that uses CustomModel.php instead of default laravel Model.php

[irman]

@ddctd143 Will this work with Laravel 5.0?

[irman]

I made the code by @ddctd143 work with Laravel 5.0 if anyone wondering:

Just change the code at line 36-37 from:

$queryType = $this->shouldRunExistsQuery($operator, $count) ? 'getRelationQuery' : 'getRelationCountQuery';

to:

$queryType = 'getRelationCountQuery';

Yes, I still use 5.0, for client's PHP version compatibility purposes. Laravel evolve so fast I can't expect all our client to have the latest PHP.

[mk-relax]

An easier workaround, without the need to change Laravel code, is to use $query->has('relation', '>', 0) instead of the (implicit) $query->has('relation', '>=', 1). That also avoids the exists query because shouldRunExistsQuery specifically checks for >= 1:

// From Illuminate\Database\Eloquent\Builder.php

protected function shouldRunExistsQuery($operator, $count)
{
    return ($operator === '>=' && $count === 1) || ($operator === '<' && $count === 1);
}

Note however that the performance will be bad! So to alleviate that, _first_ limit your result set and then use count instead of exists:

MyModel::has('relation', '>', 0)->where(...)->get(); // slow!
MyModel::where(...)->has('relation', '>', 0)->get(); // much faster.

Or, when you use scopes like function scopeHavingRelations():

MyModel::where(...)->havingRelations()->get();

[ghost]

@mk-relax This wont work.

I tried

$model = $model->has('advertable', '>', 0, 'and', function ($q) {
    $q->where('area', '>=', $this->fromArea);
});

And laravel does not know which polymorph table to use. It inserts laravel_reserved_0 instead of related table name. Attached error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'area' in 'where clause' (SQL: select count(*) as aggregate from `adverts` where (select count(*) from `adverts` as `laravel_reserved_0` where `laravel_reserved_0`.`id` = `laravel_reserved_0`.`advertable_id` and `area` >= 40 and `laravel_reserved_0`.`deleted_at` is null) > 0 and `laravel_reserved_0`.`deleted_at` is null)

[vjoao]

Still a no-fix?

[ghost]

@vjoao There won't be a fix as this issue was closed while ago.

[milewski]

+1

[pavankataria]

Is there an update for this?

I'm trying to filter on a polymorphic table.
Currently I’m able to filter through all the feeable objects…

    public function totalFees(){
        $total = 0;
        foreach ($this->fees as $fee){
            $total += $fee->feeable->fee;
        }
        return $total;
    }

but now I would like to only count the total fees from the feeable objects who only match either App\TuitionFee::class and App\FacilityFee::class.

I believe my question relates to this, right?

[wajatimur]

Did you mean filtering on relation? Have you look at constrain eager loading?

On 26 Sep 2016, at 7:58 PM, pavankataria <[email protected][e-mail]> wrote:

Is there an update for this?

I'm trying to filter on a polymorphic table.
Currently I’m able to filter through all the feeable objects…

public function totalFees(){
$total = 0;
foreach ($this->fees as $fee){
$total += $fee->feeable->fee;
}
return $total;
}
but now I would like to only count the total fees from the feeable objects who only match either App\TuitionFee::class and App\FacilityFee::class.

I believe my question relates to this, right?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/laravel/framework/issues/5429#issuecomment-249550392, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAU5IX4Q3LYmwkjqL5awBIqnOYnFgbUZks5qt7OAgaJpZM4CWoDL.

[Moutarde]

If someone is having this issue, here is a workaround : perform a filter on the collection after the where requests. Using the example given by nkeena at the beginning of this thread :

return Transaction::where(...)->get()->filter(function ($value, $key) {
    return $value->transactionable->myField == 'myValue';
});

It doesn't do exactly the same thing as a whereHas would : it gets more Transaction items in database than necessary. But it works.

[JeroenVanOort]

Should one even be allowed to execute whereHas() on a morphTo() relation while it is not working? I suggest to throw an exception for easier debugging.

And to make it work, could we add an array to the model that has the morphTo() relation to describe the possible classes it is linked to? This would enable us to add orWhereHas's for each entry in that array in the way @blankhua suggested. This would help at least some people. In case the array doesn't exist or doesn't have any entries for that relation, the exception I suggested above could be thrown.

If some people agree that this could be a viable solution, I can probably make a PR.

[JeroenVanOort]

@GrahamCampbell Is anyone seeing my latest comment? Since the issue is closed. Or should I open a new one? I'd really like some feedback on it.

[ghost]

@JeroenVanOort why to restrict ? everything works with modifications provided here by others or by me in gists. I'm using morphTo with whereHas since Laravel 5.1

[JeroenVanOort]

I think there should be a restriction when calling whereHas() on a morphTo() relation because it doesn't work, a least not out of the box. But, no restriction is needed when we can make it work, possibly the way I suggested. I think it would be a valuable addition if it can be made to work.

[ghost]

@JeroenVanOort can you submit PR to fix this issue, so that there is ability to use whereHas() on morphTo() ?

I think that restriction is not solution. After restriction would be made some of us, would need to hack code once more, so that temporary solution that is working for now would work again after restriction will be applied.

[xxzefgh]

I came up with BelongsToMorph relationship type, and got whereHas working. Obviously, it only works on a single type, so you need to have explicit relationship declaration for each type.

// Model
class Comment extends Model
{
    public function post()
    {
        return BelongsToMorph::build($this, Post::class, 'commentable');
    }

    public function note()
    {
        return BelongsToMorph::build($this, Note::class, 'commentable');
    }
}

Source: https://gist.github.com/xxzefgh/3022fee8afa53e45a6b89da3f16b3815

[dihalt]

It's very strange, that solution still is absent in official package. Hey, Taylor, want you think about this problem?

[ghost]

@dihalt This issue was closed while ago. I'm still using workaround code provided here by others, and modified by me. Laravel is now at version 5.4 and problem still exists. Maybe you could open new issue ?

[mtutumlu]

Another workaround:

Add to Transaction.php

public function reservation()
    {
        return $this->blongsTo('Reservation::class');
    }
public function project()
    {
        return $this->blongsTo('Project::class');
    }

And do:

return Transaction::where('transactionable', 'reservation')->whereHas('reservation', function($q){
        return $q->where('id', '=', '1');
    })->get();

[zlodes]

Pleeease

[rtroost]

I came across this issue today in Laravel 5.5. I'll be joining the workaround crew for now.

[DivineOmega]

Having had this problem a while ago, we wrote a composer package that extends the Eloquent Builder class and implements a work around for this issue. It's not perfect but it does the trick.

https://github.com/rapidwebltd/Improved-Polymorphic-Eloquent-Builder

It's currently designed for Laravel 5.1, but shouldn't be too hard to adapt for Laravel 5.5. Contributions welcome!

[acepsaepudin]

I searched how to fix where query with morphed table, and come to here and not solved yet :3rd_place_medal:

[amrelnaggar]

As a temporary workaround, you could declare a relationship for each type.

Transaction.php

    public function transactionable()
    {
        return $this->morphTo('transactionable');
    }
    public function reservation()
    {
        return $this->belongsTo(Reservation::class, 'transactionable_id')
            ->where('transactions.transactionable_type', Reservation::class);
    }
    public function project()
    {
        return $this->belongsTo(Project::class, 'transactionable_id')
            ->where('transactions.transactionable_type', Project::class);
    }

Reservation.php

    public function transactions()
    {
        return $this->morphMany(Transaction::class, 'transactionable');
    }

Project.php

    public function transactions()
    {
        return $this->morphMany(Transaction::class, 'transactionable');
    }

And then query the model like this:

    return Transaction::whereHas('reservation', function($q){
        return $q->where('id', '=', '1');
    })->orWhereHas('project', function($q){
        return $q->where('id', '=', '1');
    )->get();

It's not perfect but worked for me so I though I should share it in case someone has the same use case.

[flyrmyr]

Just upgraded to Laravel 5.6, this bug is still present. Can please we re-open this issue?

[ko1eda]

Just ran into this issue today as well laravel 5.6.20

[vjoao]

@flyrmyr this is a wont-fix. There is no non-hacky way of doing this.

[percymamedy]

Ran in the same problem today. If there is no fix for now maybe this could be mentioned in the documentation.

[pmochine]

Same problem with 5.6

[Braunson]

@pmochine

@thisdotvoid's solution here with BelongsToMorph seems to work without issue in 5.6.x

[pmochine]

@Braunson yes it works but only for explicit relationships.

[AdrianHL]

@thisdotvoid's solution works in Laravel 5.6.29 (implemented a few minutes ago). It will be nice to have an official way to do this in a close future :)

[groax]

polymorphic relations.... same problem here! they are a pain in my ass...

[pr-baernholdt]

Same problem here 5.7.9

[solofeed]

Same here

[hsntngr]

This party never going to end..

[staudenmeir]

Laravel 5.8.27 adds whereHasMorph(): #28928

[kakenbok]

woooooo ... i hardly can believe this! thank you so much

[Hoheckell]

stops the warning but not result set
return $this->morphMany(MODEL::class, 'name','type','pk');