Laravel中三个表的复杂查询

Question

I have a problem what I can’t solve with Laravel. I have three table:

Users, Properties, Offers

Users haveMany Properties and Offers Properties and Offers belongsTo a User

Properties table:
id
user_id
contract_start_date
other_date
…
created_at
updated_at
 
Offers table:
id
user_id
…
created_at
updated_at

I would like give a table that is like this (I use two date filter: startDate and endDate):

Users name || Properties count (They are filtered by contract_start_date)  || Properties count (They are filtered by other_date) || Offers count
———
 
user1 || 5 || 2 || 12
user2 || 0 || 1 || 0
user3 || 0 || 0 || 0
…

I try with union, leftJoin, etc but I can’t solve this… Thanks, if you can help

Answer 1

Quick and Dirty

First, get your users eager loading their properties' dates and all the offers.

$users = Users::with([
    'property'=>function($query){
        $query->select('contract_startdate','otherdate');
},
    'offer'=>function($query){
        $query->select('id');
},
);

Assuming you've properly set the $dates array in your model to include your contract_startdate and other_date. We can use carbon to filter the collection to get the properties we're interested in counting. In your view, you can:

<table>
<thead>
<tr>
<th>User</th>
<th>Property (start date)</th>
<th>Property (other date)</th>
<th>Offers</th>
</tr>
</thead>
<tbody>
   @foreach($users as $user)
<tr>
<td>{{$user->name}}</td>
<td>{{$user->properties
               ->filter(function($item) use ($filter_start,$filter_end){
                   return $item->contract_startdate->between($filter_start,$filter_end);
})->count() }}</td>
<td>{{$user->properties
               ->filter(function($item) use ($filter_start,$filter_end){
                   return return $item->other_date->between($filter_start,$filter_end);
})->count() }}</td>
<td>{{$user->offers->count()}}</td>
</tr>
   @endforeach
</tbody>
</table>

Cleaning that up

You should likely refactor the filter out of the view for cleanness, but doing so will add another loop over collection. But you might be able to remove a loop by doing something like this in your controller.

$users = Users::with([
    'property'=>function($query){
        $query->select('contract_startdate','otherdate');
},
    'offer'=>function($query){
        $query->select('id');
},
);
 
$byContractDate = collect();
$byOtherDate = collect();
 
foreach($users as $user){
    foreach($properties as $property){
        $contractCounter = 0;
        $otherCounter = 0;
        if($propery->contract_startdate->between($filter_start,$filter_end){
$contractCounter++;
}
        if($propery->contract_startdate->between($filter_start,$filter_end){
$otherCounter++;
}
}
$byContractDate->put($user->id,$contractCounter);
$byOther->put($user->id,$otherCounter);
}

And in your view:

<table>
  <thead>
     <tr>
       <th>User</th>
       <th>Property (start date)</th>
       <th>Property (other date)</th>
       <th>Offers</th>
     </tr>
   </thead>
   <tbody>          
@foreach($users as $user)
       <tr>
           <td>{{$user->name}}</td>
           <td>{{$byContract->get($user->id)}}</td>
           <td>{{$byOther->get($user->id)}}</td>
           <td>{{$user->offers->count()}}</td>
       </tr>
@endforeach
   </tbody>
</table>