我有表格:
REGIONS
id | name
----+------------------
1 | South Luzon
----+------------------
2 | North West Luzon
----+------------------
3 | North East Luzon
=====================================
BRANCHES
machinenum | name | region_id
-----------+-----------+-----------
108 | Alaminos | 1
-----------+-----------+-----------
104 | Alexander | 3
-----------+-----------+-----------
131 | Santiago | 3
-----------+-----------+-----------
114 | Apalit | 1
-----------+-----------+-----------
137 | Baliuag | 1
-----------+-----------+-----------
115 | Baguio | 2
-----------+-----------+-----------
116 | Bantay | 2
-----------+-----------+-----------
130 | San Jose | 3
=======================================
USERS
id | name | machinenum
---+-------+-------------
1 | user1 | 108
---+-------+-------------
2 | user2 | 104
---+-------+-------------
3 | user3 | 131
========================================
PENDINGS
user_id | docdate
--------+------------
2 | 2016-07-14
--------+------------
1 | 2016-07-13
--------+------------
1 | 2016-07-14
--------+------------
3 | 2016-07-13
我想要的是显示按分支和区域分组的用户发送的所有待处理。所以我的查询就像选择所有区域,并在循环中选择与region_id
匹配的分支,inner join
users
tbl 以获取user_id
,在其中,选择所有匹配的挂起的匹配user_id
,如果查询返回NOT NULL
则显示docdate
,否则显示0
。
这是我在控制器中的查询:
$regions = DB::select('SELECT * FROM regions');
foreach ($regions as $region) {
echo $region->name . "<br>";
$branches = DB::select('SELECT b.machinenum, b.name AS bname, u.id as uid
FROM branches AS b
INNER JOIN users AS u ON b.machinenum=u.machinenum
WHERE region_id=:id
ORDER BY b.name ASC',
['id' => $region->id]);
foreach ($branches as $branch) {
echo $branch->bname . "<br>";
$pendings = DB::select('SELECT * FROM pendings WHERE user_id=:id', ['id' => $branch->uid]);
if ($pendings) {
foreach ($pendings as $pending) {
echo $pending->docdate . "<br>";
}
echo "<br>";
} else {
echo "0 <br>";
}
}
echo "<br>";
}
结果将是:
South Luzon
Alaminos
2016-07-14 -- docdate
Apalit
0 -- return 0 if no pending
Baliuag
0 -- return 0 if no pending
North West Luzon
Baguio
0 -- return 0 if no pending
Bantay
0 -- return 0 if no pending
North East Luzon
Alexander
2016-07-13 -- docdate
2016-07-14 -- docdate
San Jose
0 -- return 0 if no pending
Santiago
2016-07-13 -- docdate
嗯,这正是我想要的。但是,这个逻辑在我的控制器中。我希望它在我的视野中。如何在我的视图中返回该逻辑?有什么办法吗?在我看来,我所能做的就是@foreach and @if
.
这是我目前在视图中的代码(不要介意用户和分支之间的关系,我已经在我的模型中有了它):
// Note! In controller I have:
$regions = Region::all();
$branches = Branch::all();
$pendings = Pending::all();
return view('pending.index', compact('regions', 'branches', 'pendings'));
<table class="table table-noborder table-extra-condensed">
<thead>
<tr>
<th class="custom-td text-center">Date</th>
</tr>
</thead>
<tbody>
@foreach ($regions as $region)
<tr>
<th colspan="19">{{ $region->name }}</th>
</tr>
@foreach ($branches as $branch)
@if ($region->id === $branch->region_id)
<tr>
<td>{{ $branch->name }}</td>
</tr>
@foreach ($pendings as $pending)
@if ($branch->machinenum === $pending->user->machinenum)
<tr>
<td class="custom-td text-center">{{ $pending->docdate->format('d') }}</td>
</tr>
@endif
@endforeach
@endif
@endforeach
@endforeach
</tbody>
</table>
让我们分解一下。
您可以在区域模型上映射此关系,并将其称为"挂起",因此您可以级联实现以使其更具可读性,只需调用 Region::with('branches')->get(); 并让它返回您想要的东西。看看吧。
以下是我们将如何做到这一点。
//Region.php
function branches(){
return $this->hasMany('App'Branch')->with('usermachines');
}
这是棘手的部分,让我们假设用户表是分支和挂起之间的多对多关系表,并使用分支具有ManyThrough为我们完成困难的部分:
//Branch.php
function usermachines(){
return $this->hasManyThrough('App'Pending','App'User','machinenum','user_id')->with('pendings');
}
做!完成所有这些设置后,您需要做的就是:
在控制器中:
$regions = Region::with('branches')->get();
return view('pending.index', compact('regions'));
在您看来:我正在删除您的第二个 foreach 并使用 forelse,它就像 foreach 但是当挂起为零时,使用 else,请检查一下:
<table class="table table-noborder table-extra-condensed">
<thead>
<tr>
<th class="custom-td text-center">Date</th>
</tr>
</thead>
<tbody>
@foreach ($regions as $region)
<tr>
<th colspan="19">{{ $region->name }}</th>
</tr>
@foreach ($region->branches as $branch)
<tr>
<td>{{ $branch->name }}</td>
</tr>
@forelse($branch->pendings as $pending)
<tr>
<td class="custom-td text-center">{{ $pending->docdate->format('d') }}</td>
</tr>
@empty
<tr>
<td>0</td>
</tr>
@endforelse
@endforeach
@endforeach
</tbody>