Level 67
@zafeer Please place your DB::table code inside a code block.
It looks like you are not defining e but this could just be markdown messing up your code. Let me know.
1 like
Jan 21, 2021
6
Level 3
how to compare more than one column from two different tables of database and update, if it match the same record in laravel
I want to update data of column completed after comparing the data of two columns employee_id & job_id from each of the tables resignation and request, What code do need to write in my laravel controller
*request table:*
| ID | employee_id | job_id | completed |
| ---- | ------------ |--------|-----------|
| 1 | 1 | 1 | 1 |
| 2 | 2 | 4 | 0 |
| 3 | 3 | 7 | 0 |
| 4 | 1 | 6 | 0 |
*resignation table:*
| ID | employee_id | job_id | action |
| ---- | ------------ |--------|-----------|
| 1 | 1 | 1 | 1 |
| 2 | 1 | 6 | 0 |
| 3 | 2 | 9 | 0 |
I have written the below code in my controller but it is not working
DB::table('request as q') ->leftJoin('employee as e','q ̣employee_id','=','e ̣ID') ->leftJoin('resignation as r','e ̣ID','=','r ̣employee_id') ->where('r ̣ID',$ID)->where('r ̣job_id','=','q ̣job_id') ->update([ 'q ̣completed' => '1' ]);
Level 3
okay, now i changed something in my code and trying to do this with query builder method
DB::table('request as q') ->leftJoin('employee as e','q ̣employee_id','=','e ̣ID') ->leftJoin('resignation as r','e ̣ID','=','r ̣employee_id') ->where('r ̣ID',$ID)->where('r ̣job_id','=','q ̣job_id') ->update([ 'q ̣completed' => '1' ]);
please rectify me
Level 27
If I am understanding you correctly:
Request::join('resignation', function ($join) {
$join->on('request.job_id', '=', 'resignation.job_id');
$join->on('request.employee_id', '=', 'resignation.employee_id');
})->where('request.id', '=', $ID)->update(['completed' => 1]);
1 like
Level 3
okay, now i changed something in my code and trying to do this with query builder method
DB::table('request as q') ->leftJoin('employee as e','q ̣employee_id','=','e ̣ID') ->leftJoin('resignation as r','e ̣ID','=','r ̣employee_id') ->where('r ̣ID',$ID) ->where('r ̣job_id','=','q ̣job_id') ->update([ 'q ̣completed' => '1' ]);
please rectify me
Level 27
@zafeer As @fylzero already said, it would be much better readable if you could place your code inside a code block using ``` at the top and bottom of your code and add some formatting/indentations. I will give it a try anyway:
DB::table('request as q')
->leftJoin('employee as e', 'q.employee_id', '=', 'e.id')
->leftJoin('resignation as r', 'e.id', '=', 'r.employee_id')
->where('r.id', $ID)
->where('r.job_id', '=', 'q.job_id')
->update(['completed' => 1]);
Assuming only your request table has a completed column.
Level 3
thanks for your support i got the answer from your previous post.
Please or to participate in this conversation.