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

Question

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

Markus · Accepted Answer · 2017-04-04 13:37:16Z

1

DB::table('clients')->get('clients.id') returns Collection while $expired->where('client_id', '=', '<this should be string>')

try: $expiring->where('client_id', '=', DB::table('clients')->get('clients.id')->first())->count()

However that code will not give what you want.

You should create Eloquent models and work with it instead writing your queries using DB Facades.

see here to read how to define Eloquent model https://laravel.com/docs/5.4/eloquent#defining-models

once you define the Client Eloquent model you can retrieve all clients by:

$clients = Client::whereIn('id', [1, 2, 3]); // where 1,2,3 is the client ids

answered Apr 4, 2017 at 13:37

Markus

314 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Mwai Over a year ago

Thanks for the clarification on where I was comparing two different types. However, in this snippet: $clients = Client::whereIn('id', [1, 2, 3]); // where 1,2,3 is the client ids How do I make the array of 'client_id's' for all clients with expiring or expired policies? I however got an easier way to fetch and count clients with expiring and expired policies: $with_expiring = $expiring->groupBy('client_id'); $total_with_expiring = $with_expiring->count(); $with_expired = $expired->groupBy('client_id'); $total_with_expired = $with_expired->count();

Chris Mwai · Accepted Answer · 2017-04-04 14:53:58Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related