CODEWITHSUNDEEP
phplaraveleloquent
Ashish Rawat
Ashish Rawat

Reputation: 117

Using whereHas, orWhereHas and where in a single query

Hii folks I am stuck in a problem where I need to use whereHas, orWhereHas and whereRaw , in a single query, I am new to laravel, any help would be highly appreciated.

I have three schedule status of a user => Ongoing, upcoming and completed.

This is the code I am trying to obtain result but it's not working because of orWhereHas, this orWhereHas is making it an OR operation

$schedule = ServiceRequest::whereRaw('(status =? and view_status =? and customer_id =?)', [2,1,auth_user('id')]);

$view_status = ['ongoing'=> $this->userSchedule($schedule, 2),'upcoming' => $this->userSchedule($schedule, 1),'completed' => $this->userSchedule($schedule, 3)];

here is my userSchedule function

public function userSchedule($schedule, $status)
    {
           return $schedule->whereHas('trip', function ($query) use ($status) {
                        $query->where('trips.status',$status);
                    })
                    ->orWhereHas('ad', function($query) use ($status) {
                        $query->where('post_ads.status',$status);
                    })->count(); 
    }

The sql I am getting using the above code is 

select * from `service_requests` where (status =2 and view_status =1 and customer_id =1) and exists (select * from `trips` where `service_requests`.`trip_id` = `trips`.`id` and `trips`.`status` = 2 and `trips`.`deleted_at` is null) or exists (select * from `post_ads` where `service_requests`.`post_ad_id` = `post_ads`.`id` and `post_ads`.`status` = 2 and `post_ads`.`deleted_at` is null)

The SQL query I want is

select * from `service_requests` where (status =2 and view_status =1 and customer_id =1) and (exists (select * from `trips` where `service_requests`.`trip_id` = `trips`.`id` and `trips`.`status` = 2 and `trips`.`deleted_at` is null) or exists (select * from `post_ads` where `service_requests`.`post_ad_id` = `post_ads`.`id` and `post_ads`.`status` = 2 and `post_ads`.`deleted_at` is null))

Upvotes: 0

Views: 889

Answers (1)

V-K
V-K

Reputation: 1347

public function userSchedule($schedule, $status)
{    
    return $schedule->where(function($builder) use ($status) {
        $builder->whereHas('trip', function ($query) use ($status) {
           $query->where('trips.status',$status);
        })
        ->orWhereHas('ad', function($query) use ($status) {
           $query->where('post_ads.status',$status);
        }); 
    })->count();
}

As I understand you need something like this. You can build nested queries with different depth

Upvotes: 1

Related Questions