[FIXED] Laravel Eloquent Subquery with condition

Issue

How to add a subquery to the main query with a condition? The result of the request should be similar to this:

select 
    `tasks`.*, 
    `users`.`name` as `user_name`, 
    (select count(*) from tasks_favorites on tasks_favorites.task_id = tasks.id and tasks_favorites.user_id = 38) as `is_favorite` 
from `tasks` 
left join `users` on `users`.`id` = `tasks`.`user_id` 
where 
    `tasks`.`id` = 149

I try this query but I get an error:

$task = DB::table('tasks')
        ->select(
            'tasks.*', 
            'users.name as user_name',
        )
        ->when(Auth::check(), function($query) {
            return $query->addSelect(
                DB::table('tasks_favorites')->where('tasks_favorites.task_id', 'tasks.id')->where('tasks_favorites.user_id', auth()->user()->id)->count()
            ) ;
        })
        ->leftJoin('users', 'users.id', 'tasks.user_id')
        ->where('tasks.id', $task_id)
        ->get()
        ->first() ;

Solution

did you try the selectRaw or raw method?

something like this

    $task = DB::table('tasks')
        ->select(
            'tasks.*',
            'users.name as user_name',
        )
        ->when(Auth::check(), function($query) {
            return $query->addSelect(
                DB::raw('select count(id) from tasks_favorites where tasks_favorites.task_id=tasks.id AND tasks_favorites.user_id='.auth()->user()->id.' as mycount')
            );
        })
        ->leftJoin('users', 'users.id', 'tasks.user_id')
        ->where('tasks.id', $task_id)
        ->get()
        ->first() ;

Answered By - Aria Shahdadi