Laravel Subquery with Query Builder

Question

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();

You can explain more about the database scheme and relationship we can easily.... So we Use laravel eloquent. — Saravana Sai
– Saravana Sai, Commented May 12, 2022 at 4:08

IGP · Accepted Answer · 2022-05-12 04:16:02Z

2

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();

answered May 12, 2022 at 4:16

IGP

16.4k5 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Xupitan · Accepted Answer · 2022-05-12 04:15:08Z

0

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)

edited May 12, 2022 at 4:15

answered May 12, 2022 at 4:07

Xupitan

1,6891 gold badge10 silver badges13 bronze badges

Collectives™ on Stack Overflow

Laravel Subquery with Query Builder

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related