dimanche 24 janvier 2021

How to take column from two table in whereExists clause in laravel 5.5

I have three table 1)allcasefileofuserrequest 2)allscanfileofuserrequest 3)userrequestforcasecopies

a) userrequestforcasecopies->allcasefileofuserrequest has one to many relationship

b) userrequestforcasecopies->allscanfileofuserrequest has one to many relationship c)id(primary key) in userrequestforcasecopies -> request_id(foreign key) in allcasefileofuserrequest

d)id(primary key) in userrequestforcasecopies -> request_id(foreign key) in allscanfileofuserrequest

I want to write sql query such that in where exist clause get record from allcasefileofuserrequest and allscanfileofuserrequest where approved_status is 1.Below Is my query in laravel 5.5

DB::table('userrequestforcasecopies')
                                                               
 ->where('id',$requestId)
                                                         
 ->where('first_approved_status',2)
                                                               
 ->whereExists(function($query) use($requestId)
                                                                            
 {
                                                                                
  $query->from('allcasefileofuserrequest')
                                                                                       
  ->fullJoin('allscanfileofuserrequest')
                                                                                        
  ->where('request_id', $requestId)
                                                                                        
  ->where('approved_status',1)
                                                                                        
  ->select('request_id')
                                                                                        
  ->get();
                                                                                
  })
                                                                             
                                                                
 ->update(['second_approved_status'=> 1,
                                                                        
 'second_approved_status_done_at' => Carbon::now()->format('Y-m-d H:i:s'),
                                                                        
 'updated_at' => Carbon::now()->format('Y-m-d H:i:s')

                                                                            
 ]);

But full join not work on laravel 5.5



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire