从Controller返回到View Laravel 5的逻辑

Question

I have tables:

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

What I want is to display all pending sent by users grouped by branch and region. So my query is like selecting all regions, and inside a loop select branches which matched the region_id, inner join the users tbl to get the user_id and inside that, select all pending that matched that matched the user_id and display the docdate if the query returns NOT NULL else display 0.

Here's my query inside my controller:

$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>";
        }

Result will be:

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

Well, that's exactly what I want. But, that logic is inside my controller. I want it to be in my view. How can I return that logic in my view? Is there any way to do it? All I can do in my view is @foreach and @if.

Here's my current code in my view (Don't mind the relation between users and branches, I already have it in my model):

// 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>

Answer 1

Let's break it down.

You can map this relationships on your Region model and just call it 'pendings', so you cascade the realtions to make it more readable and just call Region::with('branches')->get(); and have it return things like you want. Check it out.

Here's how we'll do it.

//Region.php
function branches(){
    return $this->hasMany('App\Branch')->with('usermachines');
}

Here's the tricky part, let's pretend the users table is a many to many relationship table between branches and pendings and use branches hasManyThrough to do the hard part for us:

//Branch.php
function usermachines(){
    return $this->hasManyThrough('App\Pending','App\User','machinenum','user_id')->with('pendings');
}

Done! With all that setup all you need to do is:

in your controller:

$regions = Region::with('branches')->get();
return view('pending.index', compact('regions'));

and in your view: I'm removing your second foreach and using a forelse, it's like foreach but with an else for when the pendings are zero, checkit out:

<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>