How to write in laravel subquery?-CodePudding

I have mysql query and now need to convert it into subquery.How can I do?

SELECT DATE(dateCreated) AS 'date' , SUM(amount) AS newPay FROM transaction_logs WHERE DATE(dateCreated) 
>= '2022-10-01' AND DATE(dateCreated) 
<= '2022-10-05' AND phone IN (SELECT phone FROM users) GROUP BY DATE(dateCreated)

CodePudding user response：

To write a subquery in Laravel, you can use the whereIn() method on a DB facade instance. The whereIn() method allows you to specify a subquery as the value for a WHERE IN clause in your SQL query.

In your case, you can use the whereIn() method to convert your MySQL query to a Laravel query like this:

$results = DB::table('transaction_logs')
    ->select(DB::raw("DATE(dateCreated) AS 'date'"), DB::raw('SUM(amount) AS newPay'))
    ->whereDate('dateCreated', '>=', '2022-10-01')
    ->whereDate('dateCreated', '<=', '2022-10-05')
    ->whereIn('phone', function($query) {
        $query->select('phone')->from('users');
    })
    ->groupBy(DB::raw('DATE(dateCreated)'))
    ->get();

In this example, the subquery is defined as an anonymous function that is passed as the second argument to the whereIn() method. The anonymous function specifies the subquery, which is a SELECT query that retrieves the phone column from the users table.

I hope this helps!