Laravel Subquery with Query Builder-CodePudding

I have a MySQL query like this:

SELECT
    a.id,
    a.nip,
    a.name,
    COUNT(
      (
        SELECT id
        FROM covis_transactions
        WHERE user_id = a.id
      )
    ) AS total_survey
FROM users a
WHERE a.user_role_id = 7
GROUP BY a.id

I tried converting it to an Eloquent query but this seems not to work:

DB::table('users as a')
    ->selectRaw("a.id, a.nip, a.name, COUNT(".DB::table('covis_transactions')->where('user_id', 'a.id').") as total_survey")
    ->where('a.user_role_id', 7)
    ->groupBy('a.id')
    ->get();

CodePudding user response：

You should create a relationship between your User model and the model for the covis_transactions table. (I'm gonna call it CovisTransaction)

# User.php
public function covis_transactions()
{
    return $this->hasMany(CovisTransaction::class);
}

Then, you can use withCount to get the aggregate count.

User::query()
    ->select('id', 'nip', 'name')
    ->withCount('covis_transactions as total_survey')
    ->where('user_role_id', 7)
    ->groupBy('id')
    ->get();

CodePudding user response：

You can convert subquery from a builder to a string :

DB::table('covis_transactions')->where('user_id', 'a.id')

DB::table('covis_transactions')->where('user_id', 'a.id')->toSql()

try it :

DB::table('users as a')
            ->selectRaw("a.id, a.nip, a.name, COUNT(" . DB::table('covis_transactions')->where('user_id', 'a.id')->toSql() . ") as total_survey")
            ->where('a.user_role_id', 7)
            ->groupBy('a.id')
            ->get();

Or can use join then count(covis_transactions.id)