Laravel Eager Loading removing empty values on children

Question

I have this eager loaded relationship:

$companies = Company::with('employees.records')->get();

If an employee has no record, I would not like to include it in the result. Here's my final query:

$companies = Company::whereIn('name', ['A', 'B'])->with([
    'employees' => function ($employee) use ($startDate, $endDate) {
        return $employee->with([
            'records' => function ($record) use ($startDate, $endDate) {
                return $record->whereBetween('date', [$startDate, $endDate]);
            }
        ]);
    }
])->get();

My desired output if there is a record or none of an employee:

{
  "data": [
    {
      "id": 1,
      "name": A,
      "records": [] 
    },
    {
      "id": 2,
      "name": B,
      "records": [
        {
          "id": 1,
          "name": "Check in at the office.",
          "date": "2018/09/08"
        }
      ] 
    }
  ]
}

Right now this is what I'm getting:

{
  "data": [
    {
      "id": 1,
      "name": A,
      "employees": [
        {
          "id": 1,
          "company_id": 1,
          "records": []
        }
      ] 
    },
    {
      "id": 2,
      "name": B,
      "employees": [
        {
          "id": 1,
          "company_id": 2,
          "records": [
            {
              "id": 1,
              "employee_id": 1,
              "name": "Check in at the office.",
              "date": "2018/09/08"
            }
          ]
        }
      ] 
    }
  ]
}

How can I modify my query to remove records if an employee have no transactions? Need some awesome help.

Answer 1

Most likely you can use Has Many Through relationship.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Company extends Model
{
    /**
     * Get all of the records for the company.
     */
    public function records()
    {
        return $this->hasManyThrough(Record::class, Employee::class);
    }
}

https://laravel.com/docs/8.x/eloquent-relationships#has-many-through

Then your query will look like this

$companies = Company::with(['records' => function ($query) use ($startDate, $endDate) {
    $query->whereBetween('date', [$startDate, $endDate]);
}])->whereIn('name', ['A', 'B'])->get();