Laravel Eloquent LEFT JOIN WHERE NULL
When building database seeds, it is common to need a query that identifies orphaned recordsâspecifically, customers that have no corresponding entries in the orders table. The raw SQL approach for this scenario is straightforward:
SELECT *
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.customer_id IS NULL
The logic here relies on the nature of a LEFT JOIN. It returns every row from the left table (customers), even if there is no match in the right table (orders). Consequently, columns originating from the orders table will contain NULL for those unmatched rows. The WHERE orders.customer_id IS NULL clause then filters the result set to show only customers without associated orders.
Attempting to replicate this directly in Laravel's Eloquent Query Builder often leads to confusing results regarding data integrity. Consider the following implementation:
$c = Customer::leftJoin('orders', function($join) {
$join->on('customers.id', '=', 'orders.customer_id');
})
->whereNull('orders.customer_id')
->first();
While this query generates the correct SQL statement, executing it might yield an object where most fields are null, retaining only specific attributes like email and phone. This behavior stems from how Eloquent handles mass assignment and the hydration of model attributes.
The root cause lies in the $fillable property defined within your Customer model. In the example provided, the model appears to be a skeleton with an empty protected $fillable = []; array. When Eloquent hydrates a model instance from a database result set, it strictly adheres to the fillable rules. If a field is not listed in $fillable, Eloquent will ignore that value and leave the attribute as its default state (which is typically null or an empty string depending on the context), regardless of what was returned by the database query.
In this specific case, if your seed data only populates email and phone for these orphaned customers, or if those are the only fields explicitly set before the join occurs, Eloquent will discard all other columns from the joined result because they are not fillable. The resulting object appears partially populated because only the non-null values that coincidentally align with your model's configuration (or global rules) survive the hydration process.
To resolve this and retrieve a fully populated Customer instance during seeding, you must explicitly define the attributes that should be mass-assigned. Update your Customer model to include all necessary fields:
class Customer extends Model {
protected $fillable = [
'id',
'first_name',
'last_name',
'email',
'phone',
'address1',
'address2',
'city',
'state',
'county',
'district',
'postal_code',
'country',
'notes',
'created_at',
'updated_at'
];
}
By populating the $fillable array, you instruct Eloquent to accept values for all columns returned by the SELECT * clause. Once this configuration is in place, the original query will return a complete model instance with all data intact.
Alternatively, if your goal is simply to find customers without orders during a seed operation and populate them with specific test data, you might consider using a subquery or a simpler relationship check. However, if you specifically need the joined data (e.g., fetching associated order totals for display purposes), ensuring the model's fillable properties are correctly configured is the most direct fix.
For more complex seeding scenarios involving relationships and data generation, tools like laravel/framework's built-in factories or external packages like fakerphp/faker can streamline the creation of these orphaned records without needing to manually manage join logic in every step. If you require deeper insights into managing database seeds within a Laravel ecosystem, resources from https://laravelcompany.com can offer additional context on best practices for data management and seeding strategies.