Add where Subquery using the model query builder

I want to do this subquery inside the where, but using the model query builder and not the default eloquent query builder.

I have this Query Builder for Message:

<?php declare(strict_types=1);

namespace App\Domains\Message\Model\Builder;

use App\Domains\Message\Model\Message as Model;
use Illuminate\Database\Eloquent\Builder;

class Message extends BuilderAbstract
{
    public function byUuid(string $uuid): self
    {
        return $this->where('uuid', $uuid);
    }

    public function byUuidBefore(string $uuid): self
    {
        return $this->where('id', '<', static fn ($q) => $q->select('id')->byUuid($uuid)->limit(1));
    }
}

This query returns: Call to undefined method Illuminate\\Database\\Query\\Builder::byUuid() because the Closure is returing the default Illuminate\Database\Query\Builder instead the model builder App\Domains\Message\Model\Builder\Message.

It's may be a Laravel bug?

Thanks!

jaseofspades88

3 years ago

Level 51

It isn't a bug. To use a subquery your should pass ONLY a closure to the where function. You're passing a string first so it doesn't treat it as a subquery. Furthermore byUuid won't be found on query builder, so you should probably create the custom scope. public scopeByUuid instead.

Here's the subquery example from the documentation note the omission of a string parameter in the where clause.

$users = User::where(function (Builder $query) {
    $query->select('type')
        ->from('membership')
        ->whereColumn('membership.user_id', 'users.id')
        ->orderByDesc('membership.start_date')
        ->limit(1);
}, 'Pro')->get();

eusonlito

3 years ago

Level 1

@jaseofspades88 I'm using a custom query builder, not the model scopes:

    /**
     * @param \Illuminate\Database\Query\Builder $query
     *
     * @return \App\Domains\Message\Model\Builder\Message
     */
    public function newEloquentBuilder($query): Builder
    {
        return new Builder($query);
    }

The subqueries also are working using the third where parameter, but the query builder returned to Closure isn't the custom for model, is the Eloquent default.

For example, this way is working:

    public function byUuidBefore(string $uuid): self
    {
        return $this->where('id', '<', static fn ($q) => $q->from('message')->select('id')->where('uuid', $uuid)->limit(1));
    }

Generating this SQL:

select * from `message` where `id` < (select `id` from `message` where `uuid` = '9ff97958-da0f-11ed-bb2d-4cedfbcaec68' limit 1);

Regards.

jaseofspades88

3 years ago

Level 51

@eusonlito Start simple and get it working using the out of the box ORM that ships with Laravel, then move it out to your 'custom query builder'. Simple approaches fix simple problems.

1 like

eusonlito

3 years ago

Level 1

@jaseofspades88 Eloquent Query Builders have a native support on Laravel. They allow move all queries related methods out of the models: martinjoo.dev/build-your-own-laravel-query-builders

dcx

3 years ago

Level 1

i agree with @jaseofspades88 But also I'm not sure what use case requires any type of custom query builder but hey maybe there is one, I would rollback from there and get it working...then maybe revise if that decision for a custom builder is really essential..

eusonlito

3 years ago

Level 1

@dcx custom query builders is la Laravel native feature. Are widely used in Laravel projects. Clean Code, models are related with data and query builders should be in a different layer, are IDE friendly by default, models are a lot of thin, no scope magic, and... beacuse a lot of more reasons 😅 I'm using this feature since Laravel 4.2 or before.

Please or to participate in this conversation.