Laravel 7.x eloquent addSelect subquery to count

user_2469528 0
Viewed 1716

Working with Laravel 7.x and Postgresql. I have a User class and GroupPivot pivot table, where multiple models can be grouped. I want to create a column off the User class with true or false if the user has a a current pivot entry against the group. This way I can list all users and know whether they are in or out of the group.

My query:

User::addSelect([
    'in_group' => GroupPivot::whereColumn('groups_pivot.pivot_id', 'users.id')
        ->where('groups_pivot.group_id', $group->id)
        ->where('groups_pivot.model', User::class)
        ->count()
     ])->get();

I have been stuck for some time with:

SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "users" LINE 1: ... "groups_pivot" where "groups_pivot"."pivot_id" = "users"."i... ^ (SQL: select count(*) as aggregate from "groups_pivot" where "groups_pivot"."pivot_id" = "users"."id" and "groups_pivot"."model" = App\Models\User)

What have I missed? Thanks.

laravel postgresql
1 Answers

This solved it. It did not like the ->count().

User::addSelect([
        'in_group' => GroupPivot::selectRaw('COUNT(*)')
           ->whereColumn('groups_pivot.pivot_id', 'users.id')
           ->where('groups_pivot.group_id', $group->id)
           ->where('groups_pivot.model', User::class)
      ])->get();
user_24695280
Related