Laravel-datatables: [QUESTION] How to get data in other tables and add it to main table column

[yuliantosb]

Summary of problem or feature request

I know this is a stupid question, I'm sorry if my question is not relevant, but I really need this feature,
somehow I got a stupid question like this,
how to get data in other tables and add it to main table column
for example I had a data like this

schedules

+-------------------------------------------------+
| instructor_id | room_id | start_at  | end_at    |
|---------------|---------|-----------|-----------|
| 1             | 1       | 08:00:00  | 09:00:00  |
|---------------|---------|-----------|-----------|
| 1             | 2       | 09:00:00  | 11:00:00  |
|---------------|---------|-----------|-----------|
| 1             | 1       | 13:00:00  | 14:00:00  |
+-------------------------------------------------+

attendances

+-------------------------------------------------+
| instructor_id | room_id | start_at  | end_at    |
|---------------|---------|-----------|-----------|
| 1             | 1       | 08:15:00  | 09:10:00  |
|---------------|---------|-----------|-----------|
| 1             | 2       | 09:12:00  | 11:20:00  |
|---------------|---------|-----------|-----------|
| 1             | 1       | 13:15:00  | 14:25:00  |
+-------------------------------------------------+

the result I want like this

+--------------------------------------------------------------------------------+
| instructor_id | room_id | start_at  | end_at    |  start_at_actual  | end_at_actual    |
|---------------|---------|-----------|-----------|-------------------|-------------------| 
| 1             | 1       | 08:00:00  | 09:00:00  | 08:15:00          | 09:10:00          |
|---------------|---------|-----------|-----------|-------------------|-------------------|
| 1             | 2       | 09:00:00  | 11:00:00  | 09:12:00          | 11:20:00          |
|---------------|---------|-----------|-----------|-------------------|-------------------|
| 1             | 1       | 13:00:00  | 14:00:00  | 13:15:00          | 14:25:00          |
+--------------------------------------------------------------------------------+

Code snippet of problem

My code like this

->addColumn('start_at_actual',function($data){
$attendance = Attendance::where('instructor_id', $data->instructor_id)
    ->where('room_id', $data->room_id)
    ->first();
return $attendance->start_at;

});

but it's not show correctly data what I want, please help me, I have no idea what should I do, I sorry for my dumb question, I'm such a sucker.

System details

• Operating System Linux Elementary OS 0.4.1 Loki
• PHP Version 7.1.9
• Laravel Version 5.5
• Laravel-Datatables Version 6.0

[yajra]

I suggest you use a join statements to achieve the results:

$query = Schedules::query()
->select([
    'schedules.instructor_id',
    'schedules.room_id',
    'schedules.start_at',
    'schedules.end_at',
    'attendances.start_at as start_at_actual',
    'attendances.end_at as end_at_actual',
])
->join('attendances', function($join) {
    $join->on('schedules.room_id', '=', 'attendances.room_id')
         ->on('schedules.instructor_id', '=', 'attendances.instructor_id')
});

return datatables($query)->toJson(); // Datatables::of($query)->make(true);

[yuliantosb]

I can't sir, because a room_id and instructor_id is not unique, maybe my mySQL is broken?

[mleppala]

If you cannot use a join that @yajra posted then how are the two table connected then? In other words, how do you know row one in the schedules table matches row one in the attendances table and not row three in the attendances table.