Reusing eloquent builder parameters inside query builder scope

I have a scope which picks out the first result per group for a set of results.

public function scopeFirstPerGroup(Builder $query, ?array $fields = null, string $by = 'id'): Builder
{
    return $query->whereIn('id', function (QueryBuilder $queryBuilder) use ($fields, $by, $query) {
        return $queryBuilder->from(static::getTable())
            ->selectRaw("min(`$by`)")
            ->groupBy($fields ?? static::$groupedScopeFields);
    });
}

This works, but it does not take into account any other conditions that were setup in the parent query. For example, the generated SQL output is:

select * from `queue_jobs` where `started_at` is null and `id` in (select min(`id`) from `queue_jobs` group by `throttle_groups`) order by `id` asc

But when the following whereNull is added to the parent query:

QueueJob::orderBy('id')
    ->whereNull('started_at')
    ->firstPerGroup(['throttle_groups'])
    ->get();

It should instead be:

select * from `queue_jobs` where `started_at` is null and `id` in (select min(`id`) from `queue_jobs` WHERE `started_at` IS NULL group by `throttle_groups`) order by `id` asc

Within the scope I need to somehow get the whereNull('started_at') (along with any other conditionals that may be added in. I somehow need to get these conditionals into the commented line below, but am unsure how.

public function scopeFirstPerGroup(Builder $query, ?array $fields = null, string $by = 'id'): Builder
{
    return $query->whereIn('id', function (QueryBuilder $queryBuilder) use ($fields, $by, $query) {
        return $queryBuilder->from(static::getTable())
            //->where... (I need to add in the the whereNull and whereType from parent query)
            ->selectRaw("min(`$by`)")
            ->groupBy($fields ?? static::$groupedScopeFields);
    });
}

ahmeddabak

6 years ago

Level 47

It should append the query inside the scope to the original query, try removing the return statement inside the callback, and then make a dump of the sql query

public function scopeFirstPerGroup(Builder $query, ?array $fields = null, string $by = 'id'): Builder
{
    return $query->whereIn('id', function (QueryBuilder $queryBuilder) use ($fields, $by, $query) {
        $queryBuilder->from(static::getTable())
            ->selectRaw("min(`$by`)")
            ->groupBy($fields ?? static::$groupedScopeFields);
    });
}

booni3

6 years ago

Level 3

This produces:

select * from `queue_jobs` where `started_at` is null and `id` in (select min(`id`) from `queue_jobs` group by `throttle_groups`) order by `id` asc

We are still just missing the

where started_at is null`

from

`id` in (select min(`id`) from `queue_jobs` group by `throttle_groups`...

ahmeddabak

6 years ago

Level 47

So you want the local scope to repeat any where clauses in the call back

booni3

6 years ago

Level 3

Thats correct, in this case. Thanks for the fast reply :-)

ahmeddabak

6 years ago

Level 47

i do not think that this is the right way to go.

    public function scopeFirstPerGroup(Builder $query, ?array $fields = null, string $by = 'id'): Builder
    {
        return $query->whereIn('id', function (QueryBuilder $queryBuilder) use ($fields, $by, $query) {
            $queryBuilder->from(static::getTable())
                ->selectRaw("min(`$by`)")
                ->groupBy($fields ?? static::$groupedScopeFields);

            foreach ($queryBuilder->getQuery()->wheres as $statement){
                $queryBuilder->where($statement['column'],$statement['operator'],$statement['value'], $statement['boolean']);
            }
        });
    }

booni3

6 years ago

Best Answer

Level 3

Thank you! That was not quite it but it got me close.

This seems to work:

public function scopeFirstPerGroup(Builder $query, ?array $fields = null, string $by = 'id'): Builder
{
    return $query->whereIn('id', function (QueryBuilder $queryBuilder) use ($fields, $by, $query) {
        $queryBuilder->from(static::getTable())
            ->selectRaw("min(`$by`)")
            ->groupBy($fields ?? static::$groupedScopeFields);

            $queryBuilder->addNestedWhereQuery($query->getQuery());
    });
}

ahmeddabak

6 years ago

Level 47

Amazing, please mark this as solved

Please or to participate in this conversation.