Laravel 8中的外部联接3个或更多表 [英] Outer join 3 or more tables in Laravel 8
问题描述
This topic is related to my previews one Join two tables with all records
我现在正在尝试在Laravel控制器代码中联接3个或更多表，并在一个Datatable中查看它们.
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 |
+--------------------+---------+
需要的结果是这样:
+--------------------+---------+---------------+---------------+
| 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.
我为2个表创建代码.如上表所示，他正在为我们工作:
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();
我现在尝试在 $ results3 变量中添加第3列，并将其与其他变量合并:
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();
这使我所有需要在 tempoutstamb2 列中的记录在 tempoutstamb 中错误地传输.任何想法如何使它正确吗?
This gives me all records that need to be in column tempoutstamb2 are transfered wrongly in tempoutstamb. Any Idea how to make it right?
原始的SQL答案也很好.
Raw SQL answer is good too.
推荐答案
您可以通过更简单的方式解决它:
You can solve it in easier way:
select ts recordtime, max(to1) tempout, max(to2) tempoutstamb, max(to3) tempoutstamb2
from (
select ts, tempout to1, cast (null as numeric(10,1)) to2, cast (null as numeric(10,1)) to3
from table1
union all
select ts, null, tempout, null
from table2
union all
select ts, null, null, tempout
from table3
) tt
group by ts
order by ts;
您可以在小提琴中找到它 https://www.db-fiddle.com/f/eJsPZijRnQFGXugLGHnn93/0
You can find it in fiddle https://www.db-fiddle.com/f/eJsPZijRnQFGXugLGHnn93/0
注意:我假设显示为'-'的空值只是输出格式.如果不是这种情况，则带有NULL的输出可以转换为'-'.
NOTE: I have assumed that null values displayed as '-' is just output formatting. If that is not the case output with NULL can be converted to '-'.
注2:我不知道如何转换为Laravel/PHP代码，希望您有更好的主意.
NOTE2: I do not know how to convert to Laravel/PHP code, hopefully you would have better idea.
这篇关于Laravel 8中的外部联接3个或更多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
