Add where Subquery using the model query builder

[eusonlito]

I have a Message Model with a custom Query Builder:

<?php declare(strict_types=1);

namespace App\Domains\Message\Model;

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

class Message extends Model
{
    /**
     * @var string
     */
    protected $table = 'message';

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

The Model Query Builder is:

<?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 Builder
{
    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 method returns the error:

Call to undefined method Illuminate\\Database\\Query\\Builder::byUuid()

The problem is the third parameter in where, because is filled with the default Database Builder, instead the current Model Builder.

Following the framework code, I get:

The where is moved from Eloquent to default Query builder:

framework/src/Illuminate/Database/Eloquent/Builder.php

Lines 290 to 301 in 81c61bd

	public function where($column, $operator = null, $value = null, $boolean = 'and')
	{
	if ($column instanceof Closure && is_null($operator)) {
	$column($query = $this->model->newQueryWithoutRelationships());
	$this->query->addNestedWhereQuery($query->getQuery(), $boolean);
	} else {
	$this->query->where(...func_get_args());
	}
	return $this;
	}

Now the Query Builder is the default:

framework/src/Illuminate/Database/Query/Builder.php

Lines 794 to 799 in 81c61bd

	// If the value is a Closure, it means the developer is performing an entire
	// sub-select within the query and we will need to compile the sub-select
	// within the where clause to get the appropriate query record results.
	if ($value instanceof Closure) {
	return $this->whereSub($column, $operator, $value, $boolean);
	}

And here where the Callback is called:

framework/src/Illuminate/Database/Query/Builder.php

Lines 1660 to 1663 in 81c61bd

	// Once we have the query instance we can simply execute it so it can add all
	// of the sub-select's conditions to itself, and then we can cache it off
	// in the array of where clauses for the "main" parent query instance.
	$callback($query = $this->forSubQuery());

The only way that I have to fix this is creating the query using the Database Builder:

    /**
     * @param string $uuid
     *
     * @return self
     */
    public function byUuidBefore(string $uuid): self
    {
        return $this->where('id', '<', fn ($q) => $this->selectIdByLastUuid($q, $uuid));
    }

    /**
     * @param \Illuminate\Database\Query\Builder $q
     * @param string $uuid
     *
     * @return \Illuminate\Database\Query\Builder
     */
    public function selectIdByLastUuid($q, string $uuid): Builder
    {
        return $q->select('id')
            ->from('message')
            ->where('uuid', $uuid)
            ->orderBy('id', 'DESC')
            ->limit(1);
    }

There are some more "Elegant" solution?

Thanks!
Lito.

[cosmastech]

Could you put up a public repo with just enough code to display this issue?

[eusonlito]

ASAP :)

[Angel5a]

Why not to use dynamic scopes?

[eusonlito]

Clean code, models are related with data, query builders should be in a different layer, query builders are IDE friendly by default, models a lot of thin, no scope magic, beacuse a lot of reasons 😅