Laravel Eloquent LEFT JOIN WHERE NULL

Question

I'm trying to use Eloquent to perform the following query during a database seed:

SELECT
    *
FROM
    customers
LEFT JOIN
    orders
    ON customers.id = orders.customer_id
WHERE
    orders.customer_id IS NULL

And here is my implementation in Eloquent:

$c = Customer::leftJoin('orders', function($join) {
      $join->on('customers.id', '=', 'orders.customer_id');
    })
    ->whereNull('orders.customer_id')
    ->first();

Whereas the first query always returns full results, the Eloquent equivalent always returns empty elements for everything but the email and phone fields of the customers table. I'm at a loss to explain this since the Customers and Orders models are both artisan generated skeletons.

Ex:

class Customer extends \Eloquent {

    // Add your validation rules here
    public static $rules = [
        // 'title' => 'required'
    ];

    // Don't forget to fill this array
    protected $fillable = [];

}

Here is the array that is output when I dd() the first Eloquent query on a seed (generated originally by Faker):

protected $original =>
  array(25) {
    'id' =>
    NULL
    'first_name' =>
    NULL
    'last_name' =>
    NULL
    'email' =>
    string(24) "[email protected]"
    'phone' =>
    string(17) "642.150.9176x5684"
    'address1' =>
    NULL
    'address2' =>
    NULL
    'city' =>
    NULL
    'state' =>
    NULL
    'county' =>
    NULL
    'district' =>
    NULL
    'postal_code' =>
    NULL
    'country' =>
    NULL
    'notes' =>
    NULL
    'created_at' =>
    NULL
    'updated_at' =>
    NULL
    'customer_id' =>
    NULL
    'total' =>
    NULL
}

Solution 1

This can be resolved by specifying the specific column names desired from the specific table like so:

$c = Customer::leftJoin('orders', function($join) {
      $join->on('customers.id', '=', 'orders.customer_id');
    })
    ->whereNull('orders.customer_id')
    ->first([
        'customers.id',
        'customers.first_name',
        'customers.last_name',
        'customers.email',
        'customers.phone',
        'customers.address1',
        'customers.address2',
        'customers.city',
        'customers.state',
        'customers.county',
        'customers.district',
        'customers.postal_code',
        'customers.country'
    ]);

Solution 2

You can also specify the columns in a select like so:

$c = Customer::select('*', DB::raw('customers.id AS id, customers.first_name AS first_name, customers.last_name AS last_name'))
->leftJoin('orders', function($join) {
  $join->on('customers.id', '=', 'orders.customer_id') 
})->whereNull('orders.customer_id')->first();

Solution 3

Although Other Answers work well, i want to give you alternate short version which i use very often:

Customer::select('customers.*')
        ->leftJoin('orders', 'customers.id', '=', 'orders.customer_id')
        ->whereNull('orders.customer_id')->first();

And as in laravel version 5.3 added one more feature which will make your work even simpler look below for example:

Customer::doesntHave('orders')->get();

Solution 4

I would dump your query so you can take a look at the SQL that was actually executed and see how that differs from what you wrote.

You should be able to do that with the following code:

$queries = DB::getQueryLog();
$last_query = end($queries);
var_dump($last_query);
die();

Hopefully that should give you enough information to allow you to figure out what's gone wrong.

View more solutions

Comments

[eComEvo]

Ran it and the queries are identical. When I run them in MySQL Workbench directly I get the expected results. This tells me the problem lies in some facet of the way Laravel handles the retrieved data.

[eComEvo]

I just realized that the NULL fields from the orders table row that has yet to be created are overwriting the values in the customers table that has values. The only difference between the customers and orders tables is that the orders table does not have an email or phone field, so that value is not overwritten. I've never seen this behavior before. How do I prevent Laravel's Eloquent from doing this?

[Basheer Kharoti]

You could also specify to get all columns something like customers.*

[tishma]

Aliases e.g. customers.id as customer_id are also possible for better readability.