Select without laravel_through_key doesnt return query result with PHP while query direct on db does

[ssglopes]

Hi, I have the below described issue and tried to find a solution, but without any success sofar. Any tips or help would be much appreciated.

1st example, this code:

        return $this->hasManyDeep(
                Tag::class,
                [Post::class, 'taggables'],
                ['author_id', ['taggable_type', 'taggable_id'], 'id'],
                [null, null, 'tag_id']
            )
            ->selectRaw('count(*) as "posts_count"')
            ->withoutGlobalScope(WithCountsScope::class)
            ->groupByRaw('"tags"."id", "posts"."author_id"');

Generates this query:

select
  "tags"."id",
  "tags"."name",
  count("posts"."id") as "posts_count",
  "posts"."author_id" as "laravel_through_key"
from
  "tags"
  inner join "taggables" on "taggables"."tag_id" = "tags"."id"
  inner join "posts" on "posts"."id" = "taggables"."taggable_id"
where
  "taggables"."taggable_type" = 'App\Models\Post'
  and "posts"."author_id" in (2,3)
  and "tags"."deleted_at" is null
  and "tags"."active" = true
  and "posts"."deleted_at" is null
group by
  "tags"."id",
  "posts"."author_id"
order by "tags"."id" 

And Dbeaver outputs this result:

2nd example, this code

        return $this->hasManyDeep(
                Tag::class,
                [Post::class, 'taggables'],
                ['author_id', ['taggable_type', 'taggable_id'], 'id'],
                [null, null, 'tag_id']
            )
            ->withCustomThroughKeyCallback(fn() => 'tags.*')
            ->selectRaw('count(*) as "posts_count"')
            ->withoutGlobalScope(WithCountsScope::class)
            ->groupByRaw('"tags"."id", "posts"."author_id"');

Generates this query:

select
  "tags"."id",
  "tags"."name",
  count("posts"."id") as "posts_count"
from
  "tags"
  inner join "taggables" on "taggables"."tag_id" = "tags"."id"
  inner join "posts" on "posts"."id" = "taggables"."taggable_id"
where
  "taggables"."taggable_type" = 'App\Models\Post'
  and "posts"."author_id" in (2,3)
  and "tags"."deleted_at" is null
  and "tags"."active" = true
  and "posts"."deleted_at" is null
group by
  "tags"."id"
order by "tags"."id"  

And DBeaver outputs this result:

Its this last query result I want to retrieve with the second code example. With Dbeaver both queries output data but when using the php-code the second query doesnt. I think its due to laravel_through_key not being there. There reason i dont want to have it in the select is that I need the sum of for example ROW-ID 111 or 115. The laravel_through_key shows the author_id and the posts_count for each author, as show in the first example, but I need to sum of the posts_count per tag-id as in the second example.

[staudenmeir]

Hi @ssglopes,

You can use getQuery() to get the underlying query builder and prevent the laravel_through_key column from being added:

public function tags()
{
    return $this->hasManyDeep(
        Tag::class,
        [Post::class, 'taggables'],
        ['author_id', ['taggable_type', 'taggable_id'], 'id'],
        [null, null, 'tag_id']
    );
}

$author->tags()
    ->getQuery()
    ->withoutGlobalScope(WithCountsScope::class)
    ->select('tags.id', 'tags.name')
    ->selectRaw('count(*) as "posts_count"')
    ->groupBy('tags.id', 'posts.author_id')
    ->get();

[ssglopes]

Hi @staudenmeir , thanks for your fast reply, that does indeed do the trick to get the correct query. Only now I get this error:

"detail": "Call to undefined method Illuminate\\Database\\Eloquent\\Builder::addEagerConstraints()",

which is, i think, due to being now returned:

Illuminate\Database\Eloquent\Builder

instead of :

Staudenmeir\EloquentHasManyDeep\HasManyDeep

[staudenmeir]

What does your eager loading query look like?

[ssglopes]

Hello @staudenmeir ,

The adjusted method by the recommendations you gave me causing the last above mentioned error now looks like this (regardless of its with and without ->get() on the end). It throws the error so I can not check the query in telescope so i used toRawSql() to see the query.

API url: /roles/3/users?include=tags

    public function tags()
    {      
        $test = $this->hasManyDeep(
                Tag::class,
                [Post::class, 'taggables'],
                ['author_id', ['taggable_type', 'taggable_id'], 'id'],
                [null, null, 'tag_id']
            )
            ->getQuery()
            ->withoutGlobalScope(WithCountsScope::class)
            //->withCustomThroughKeyCallback(fn() => ['tags.*', 'tags.id as laravel_through_key'])
            ->select('tags.id', 'tags.name')
            ->selectRaw('count(*) as "posts_count"')
            //->groupByRaw('"tags"."id", "posts"."author_id"');
            ->groupBy('tags.id')
            //->toRawSql()
            ->get()
            ;
        //dd($test);
        return $test;
    }

The query outputted by toRawSql():

select 
  "tags"."id", "tags"."name", count(*) as "posts_count" 
from 
  "tags" 
inner join "taggables" on "taggables"."tag_id" = "tags"."id" 
inner join "posts" on "posts"."id" = "taggables"."taggable_id" 
where 
  "taggables"."taggable_type" = 'App\Models\Post' 
and "tags"."deleted_at" is null 
and "tags"."active" = 1 
and "posts"."deleted_at" is null 
group by "tags"."id"

If I call the endpoint again but with old method I wrote initially can see the actual query in telescope:

    public function tags()
    {      
        $test = $this->hasManyDeep(
                Tag::class,
                [Post::class, 'taggables'],
                ['author_id', ['taggable_type', 'taggable_id'], 'id'],
                [null, null, 'tag_id']
            )
            //->getQuery()
            ->withoutGlobalScope(WithCountsScope::class)
            ->withCustomThroughKeyCallback(fn() => ['tags.*', 'tags.id as laravel_through_key'])
            //->select('tags.id', 'tags.name')
            ->selectRaw('count(*) as "posts_count"')
            //->groupByRaw('"tags"."id", "posts"."author_id"');
            ->groupBy('tags.id')
            //->toRawSql()
            //->get()
            ;
        //dd($test);
        return $test;
    }

Query from telescope:

select
  "tags"."id",
  "tags"."name",
  count(*) as "posts_count",
  "posts"."author_id" as "laravel_through_key"
from
  "tags"
  inner join "taggables" on "taggables"."tag_id" = "tags"."id"
  inner join "posts" on "posts"."id" = "taggables"."taggable_id"
where
  "taggables"."taggable_type" = 'App\Models\Post'
  and "posts"."author_id" in (2, 3)
  and "tags"."deleted_at" is null
  and "tags"."active" = 1
  and "posts"."deleted_at" is null
group by
  "tags"."id",
  "posts"."author_id"

But the query I actually want to have should look like this:

select
  "tags"."id",
  "tags"."name",
  count(*) as "posts_count"
from
  "tags"
  inner join "taggables" on "taggables"."tag_id" = "tags"."id"
  inner join "posts" on "posts"."id" = "taggables"."taggable_id"
where
  "taggables"."taggable_type" = 'App\Models\Post'
  and "posts"."author_id" in (2, 3)
  and "tags"."deleted_at" is null
  and "tags"."active" = 1
  and "posts"."deleted_at" is null
group by
  "tags"."id"

So I do achieve with getQuery() to adjust the query but itself but then it fails becuase of the fatal error. Which I suspect its because the adjusted method with getQuery() returns now a Builder instance instead of a Relation instance causing the fatal error to be trhown.

Thanks

[staudenmeir]

Are you executing a query with ->with('tags') or ->load('tags')?

[ssglopes]

It hits this method https://github.com/laravel-json-api/eloquent/blob/01aa76ac3abcff97fd713e0bbf01c246f6dd1f5d/src/QueryBuilder/JsonApiBuilder.php#L227 which is a part of this package.

I put a dd() inside the below method and from that i can answer your question that it hits the ->with('')

    public function with($includePaths): self
    {
        $includePaths = IncludePaths::nullable($includePaths);

        $loader = new EagerLoader(
            $this->schemas,
            $this->schema,
            $includePaths,
        );
// ----------
        dd($includePaths);
// ----------
        $this->query->with($paths = $loader->getRelations());

        foreach ($loader->getMorphs() as $name => $map) {
            $this->query->with($name, static function(EloquentMorphTo $morphTo) use ($map) {
                $morphTo->morphWith($map);
            });
        }

        $this->eagerLoading = (!empty($paths) || !empty($map));
        $this->parameters->setIncludePaths($includePaths);

        return $this;
    }

LaravelJsonApi\Core\Query\IncludePaths {#2956 // vendor/laravel-json-api/eloquent/src/QueryBuilder/JsonApiBuilder.php:232
  -stack: array:1 [
    0 => 
LaravelJsonApi\Core\Query
\
RelationshipPath {#2959
      -names: array:1 [
        0 => "tags"
      ]
    }
  ]
}

[staudenmeir]

Which I suspect its because the adjusted method with getQuery() returns now a Builder instance instead of a Relation instance causing the fatal error to be trhown.

Yes, the query is no longer relationship and can't be used for eager loading. I don't see a way to make your custom query with eager loading.

[ssglopes]

Ok thanks for your help regardless and will try to solve it in a different way.