How to use custom query scopes in an Eloquent subselect query?

Question

This works by manually getting the data as an array then repassing it:

public function scopeWhereWhitelisted($query, $value=true, Tenant $tenant)
{
    return $query->where(function($query)use($value,$tenant)
    {
        $user_id_list = $tenant->getWhiteListedUsersGroup()
                                                ->users()
                                                ->select('users.id')
                                                ->lists('id')
                                                ->all()
        ;

        $query->{ $value ? 'whereIn' : 'whereNotIn' }('users.id',$user_id_list);
    });
}

But I want this to work (comment // indicates the only difference):

public function scopeWhereWhitelisted($query, $value=true, Tenant $tenant)
{
    return $query->where(function($query)use($value,$tenant)
    {
        $user_id_list = $tenant->getWhiteListedUsersGroup()
                                                ->users()
                                                ->select('users.id')
                                                //->lists('id')
                                                //->all()
        ;

        $user_id_list = $tenant->getWhiteListedUsersGroup()->users()->select('users.id');//->lists('id')->all();
        $query->{ $value ? 'whereIn' : 'whereNotIn' }('users.id',$user_id_list);
    });
}

I want to be able to create a "real" subselect without having to have duplicate copies of custom query scopes and relationship queries just for each scope. $tenant->getWhiteListedUsersGroup()->users() is a many-to-many relationship

Here is an example of what has to be done to get a real subselect:

public function scopeWhereWhitelisted($query, $value=true, Tenant $tenant)
{
    return $query->where(function($query)use($value,$tenant)
    {
        $query->{ $value ? 'whereIn' : 'whereNotIn' }('users.id',function($query)
        {
             $query->from('groups_memberships')
                  //     recreating an existing relationship function
                  ->join('groups','groups.id','group_memberships.group_id')
                  ->select('users.id')
                  //     recreating an already existing query scope
                  ->whereNull('deleted_at')
             ;
        });
    });
}

This question will most likely apply to both Laravel 4.0+ and 5.0+
This question is NOT answered by How to do this in Laravel, subquery where in
Restructing code so that the query starts from intended sub query will not work as soon as I need a second non-trivial subselect.
The inclusion/exclusion of ->getQuery() has not made a difference.
I have to choose between a fake subselect or non-DRY custom query scopes.
It seems that the main issue is that the subselect engine is forcing me to use a pre-existing $query object that can't be initialized from an existing relationship.
The recreation of soft deletes (whereNull('deleted_at')) is a trivial example, but I might have to recreate a queryscope that could be relatively complicated already.

RDelorier · Answer 1 · 2015-11-07T00:51:13.900

0

Is this whats your going after?

$value; //true or false

$tenant->whereHas('user', function($query) use ($value){
    $query->whereHas('groupMembership', function($query) use ($value){
        $query->whereHas('group', function($query) use ($value){
            if($value){ $query->onlyTrashed(); )
        });
    })
})

This assumes the group relation includes a withTrashed() call on the relation

edited Nov 07 '15 at 00:51

answered Nov 07 '15 at 00:38

RDelorier

737
6
8

`whereHas` is strictly off the table. Each `whereHas` calculates a temporary column for each table. This means that indexes cannot be used and there will be a full table scan. You've got three `whereHas` nested, which means that for every row in the user table, MySQL has to do calculate the "has groupMembership" tmp column. To determine that, each full table scan of groupMember will perform a full table scan of group. That is essentially 3 layers of exponentially growing full table scans. https://github.com/laravel/framework/issues/3543 – InstanceOfMichael Nov 09 '15 at 23:52

How to use custom query scopes in an Eloquent subselect query?

1 Answers1