Search code examples
phpmysqllaravellaravel-5laravel-5.3

Laravel Eloquent ORM method for Retrieving Multiple Rows based on an Array of ids

I might not have phrased the question the way I wanted to but here's my Dilema:

I have a table of users named 'clients' with the structure below:

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg .tg-yw4l{vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-yw4l">id</th>
    <th class="tg-yw4l">int(10)</th>
    <th class="tg-031e">unsigned</th>
  </tr>
  <tr>
    <td class="tg-yw4l">client_name</td>
    <td class="tg-yw4l">varchar(255)</td>
    <td class="tg-yw4l"></td>
  </tr>
  <tr>
    <td class="tg-yw4l">id_number</td>
    <td class="tg-yw4l">int(11)</td>
    <td class="tg-yw4l"></td>
  </tr>
  <tr>
    <td class="tg-yw4l">email</td>
    <td class="tg-yw4l">varchar(255)</td>
    <td class="tg-yw4l"></td>
  </tr>
</table>

I have another table for policies 'policies' with the structure below:

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg .tg-yw4l{vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-yw4l">id</th>
    <th class="tg-yw4l">int(10)</th>
    <th class="tg-yw4l">unsigned</th>
    <th class="tg-031e">AUTO INCREMENT</th>
  </tr>
  <tr>
    <td class="tg-yw4l">client_id</td>
    <td class="tg-yw4l">int(10)</td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
  </tr>
  <tr>
    <td class="tg-yw4l">type_id</td>
    <td class="tg-yw4l">int(11)</td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
  </tr>
  <tr>
    <td class="tg-yw4l">product_id</td>
    <td class="tg-yw4l">int(11)</td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
  </tr>
</table>

The 'client_id' in the 'policies' table is the equivalent of the 'id' in the 'clients' table. A client can have multiple 'policies'. When a policy has expired, I want to fetch a list of clients with expired policies. The logic is not every user will have an expired or expiring policy but one user can have multiple expiring and expired policies. (For example, in a table of 10 clients, and a total number of 20 expiring or expired policies, there can be 5 clients with expiring or expired policies etc.) Every policy is tied to one client. That client can have an unlimited number of policies

How should I count the number of clients with expiring policies considering the client's 'id' is equal to the 'client_id' in the policies table?

Here's what I have tried so far:

$today = Carbon::today()->toDateString();
$yesterday = Carbon::yesterday()->toDateString();
$expiry_period = Carbon::today()->addDays(3)->toDateString();

$client = DB::table('clients')->join('active_policies', 'clients.id', '=', 'active_policies.client_id')->select('clients.id');
$active_clients = Clients::all();

$policies = ActivePolicies::all();
$total_policies = $policies->count();

$expiring = $policies->where('renewal_date', '<=', $expiry_period)->where('renewal_date', '>=', $today);
$total_expiring = $expiring->count();

$expired = $policies->where('renewal_date', '<=', $yesterday);
$total_expired = $expired->count();

//here's where I try to count the clients with expiring policies.
$with_expiring = $expiring->where('client_id', '=', DB::table('clients')->get('clients.id'))->count();

//here's where I try to count the clients with expired policies.
$with_expired = $expired->where('client_id', '=', DB::table('clients')->get('clients.id'))->count();

I get the following error upon execution:

Type error: Argument 1 passed to Illuminate\Database\Grammar::columnize() must be of the type array, string given, called in /var/.../app/vendor/laravel/framework/src/Illuminate/Database/Query/Grammars/Grammar.php on line 131

Solution

  • This seems to do the trick:

    /*groups the expiring policies for each user*/
$with_expiring = $expiring->groupBy('client_id');

/*counts the number of clients with expiring policies instead of
 *counting the number of expiring policies first then checking
 *against a list of 'client_id's
 */
$total_with_expiring = $with_expiring->count();

/*groups the expired policies for each user*/
$with_expired = $expired->groupBy('client_id');

/*counts the number of clients with expired policies instead of
 *counting the number of expired policies first then checking
 *against a list of 'client_id's
 */
$total_with_expired = $with_expired->count();

    There's still the issue of ensuring the 'client_id' really matches with the 'id' in the clients table.