This topic is related to my previews one Join two tables with all records
I'm tryng now to join 3 or more tables in my Laravel controller code, and view them in one Datatable.
table1
+--------------------+---------+
| recordtime | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 | 1.1 |
| 4.12.2020 10:30:00 | 1.2 |
| 4.12.2020 11:00:00 | 1.3 |
| 4.12.2020 11:30:00 | 1.4 |
| 4.12.2020 12:00:00 | 1.5 |
+--------------------+---------+
table2
+--------------------+---------+
| recordtime | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 | 2.1 |
| 4.12.2020 11:00:00 | 2.3 |
| 4.12.2020 12:00:00 | 2.5 |
| 4.12.2020 13:00:00 | 2.6 |
| 4.12.2020 14:00:00 | 2.7 |
| 4.12.2020 16:00:00 | 2.9 |
+--------------------+---------+
table3
+--------------------+---------+
| recordtime | tempout |
+--------------------+---------+
| 4.12.2020 15:00:00 | 3.1 |
| 4.12.2020 16:00:00 | 3.3 |
+--------------------+---------+
The result needed is this:
+--------------------+---------+---------------+---------------+
| recordtime | tempout | tempoutstamb | tempoutstamb2 |
+--------------------+---------+---------------+---------------+
| 4.12.2020 10:00:00 | 1.1 | 2.1 | - |
| 4.12.2020 10:30:00 | 1.2 | - | - |
| 4.12.2020 11:00:00 | 1.3 | 2.3 | - |
| 4.12.2020 11:30:00 | 1.4 | - | - |
| 4.12.2020 12:00:00 | 1.5 | 2.5 | - |
| 4.12.2020 13:00:00 | - | 2.6 | - |
| 4.12.2020 14:00:00 | - | 2.7 | - |
| 4.12.2020 15:00:00 | - | - | 3.1 |
| 4.12.2020 16:00:00 | - | 2.9 | 3.3 |
+--------------------+---------+---------------+---------------+
The result need to have all records and is based on "recordtime" column.
I create the code for 2 tables. He is working us expected like in the table above:
$results2 = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
->selectRaw('table1.recordtime')
->selectRaw('max(table1.tempout) as tempout')
->selectRaw('max(table2.tempout) as tempoutstamb')
->leftJoin('table2', function($join){
$join->on('table1.recordtime', '=', 'table2.recordtime');
})
->groupBy('table1.recordtime');
$results = Tablemodel2::whereBetween('table2.recordtime', $dateScope)
->selectRaw('table2.recordtime')
->selectRaw('max(table1.tempout) as tempout')
->selectRaw('max(table2.tempout) as tempoutstamb')
->leftJoin('table1', function($join){
$join->on('table1.recordtime', '=', 'table2.recordtime');
})
->groupBy('table2.recordtime')
->orderBy('recordtime', 'ASC')
->union($results2)
->get();
I tryed now to add the 3th column in $results3
variable and union it with others:
$results2 = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
->selectRaw('table1.recordtime')
->selectRaw('max(table1.tempout) as tempout')
->selectRaw('max(table2.tempout) as tempoutstamb')
->leftJoin('table2', function($join){
$join->on('table1.recordtime', '=', 'table2.recordtime');
})
->groupBy('table1.recordtime');
$results3 = Tablemodel3::whereBetween('table3.recordtime', $dateScope)
->selectRaw('table3.recordtime')
->selectRaw('max(table1.tempout) as tempout')
->selectRaw('max(table3.tempout) as tempoutstamb2')
->leftJoin('table1', function($join){
$join->on('table3.recordtime', '=', 'table2.recordtime');
})
->groupBy('table3.recordtime');
$results = Tablemodel2::whereBetween('table2.recordtime', $dateScope)
->selectRaw('table2.recordtime')
->selectRaw('max(table1.tempout) as tempout')
->selectRaw('max(table2.tempout) as tempoutstamb')
->leftJoin('table1', function($join){
$join->on('table1.recordtime', '=', 'table2.recordtime');
})
->groupBy('table2.recordtime')
->orderBy('recordtime', 'ASC')
->union($results2)
->union($results3)
->get();
This gives me all records that need to be in column tempoutstamb2
are transfered wrongly in tempoutstamb
. Any Idea how to make it right?
Raw SQL answer is good too.