Sql query incorrect with getModel()->{relationship_name}

@nonoo You can try this:

For single user

$contractQuery = User::find(1)->active_contract();  // Assuming you're looking at user with id=1
dd($contractQuery->toSql());

For All user

$contractQuery = User::has('active_contract');
dd($contractQuery->toSql());

Thanks for the answer but the struggle is only when using the ->getModel() method and it's the only way I found to make a dynamic code on a Builder instance (for example in an class extention like Sort of the Spatie query builder library or to handle dynamic requests generation). I gave a very simple exemple of how to reproduce the issue to show what is happening but the final use case is much more complex.

Actually I'm looking for a way to fix it in the eloquent mecanics or an alternative to use the getModel() to generate a well formed Sql query based on a Builder instance to get a dynamic nested relationships query.

In my use case, I'm trying to make an extension of the Sort class of spatie query builder that will handle nested relationships with even custom ones (like hasOne()->ofMany() or things like that) and generate a sub select to use a orderBy() on it. In some cases, we can't use join because it breaks the query the I'm doing this class to handle the sorting in this specific case.

here's a work in progress (with some tests) version if you want to understand better :

<?php

namespace App\Customs\QueryBuilder;

use Spatie\QueryBuilder\Sorts\Sort;
use Illuminate\Database\Eloquent\Builder;

class RelatedSort implements Sort
{

    public function __invoke(Builder $query, bool $descending, string $property)
    {
        $pieces = explode(".", $property);
        $columnName = array_pop($pieces); // remove the column name from the end

        $baseModel = $query->getModel();

        // Resolve each relationship and add constraints
        $relationQueries = [];
        foreach ($pieces as $relationName) {
            if (!method_exists($baseModel, $relationName)) {
                throw new \Exception("Relation {$relationName} does not exist on the model " . get_class($baseModel));
            }

            $relation = $baseModel->$relationName();
            $relationQuery = $relation->getRelated()->newQuery();
            $relationBaseQuery = $relation->newQuery()->getQuery();
            if (method_exists($relation, 'getBaseQuery')) {
                $rbq = $relationBaseQuery->getQuery();
                foreach ($rbq->beforeQueryCallbacks as $callback) {
                    $callback($relationQuery);
                }
            }
            if (
                $relation instanceof \Illuminate\Database\Eloquent\Relations\HasOne ||
                $relation instanceof \Illuminate\Database\Eloquent\Relations\HasMany
            ) {
                $relationQuery->whereColumn(
                    $relation->getQualifiedForeignKeyName(),
                    $baseModel->getQualifiedKeyName()
                );
            } elseif ($relation instanceof \Illuminate\Database\Eloquent\Relations\BelongsTo) {
                $relationQuery->whereColumn(
                    $relation->getQualifiedOwnerKeyName(),
                    $relation->getForeignKeyName()
                );
            }

            $baseModel = $relation->getRelated();
            $relationQueries[] = $relationQuery;
        }

        // Build the nested subquery from the deepest relationship outward
        $finalQuery = array_pop($relationQueries)->select($columnName);
        while ($relationQueries) {
            $parentQuery = array_pop($relationQueries);
            $parentQuery->selectSub($finalQuery, 'nested_column');
            $finalQuery = $parentQuery;
        }

        $subqueryAlias = 'subquery_' . str_replace('.', '_', $property);
        $query->selectSub($finalQuery, $subqueryAlias);
        $query->orderBy($subqueryAlias, $descending ? 'desc' : 'asc');
        dump($query->toSql());
    }

But this is just where I noticed about this behavior of eloquent, I tried many other ways but I couldn't get a well formed Sql query using the getModel() because by design, it adds two where clause in the getModel()->getQuery() and these where clauses are : <field id> IS NOT NULL and <field id> = ? but as the bindings of this where is null, the result become : <field id> IS NULL AND <field id> IS NOT NULL

I think it's a bug because we should be able to generate a well formed query from a getModel() result (or at least, have another method that allows to generate it). Can someone confirm if it's a bug or not in this specific case please?