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) "luther.braun@example.org"
    '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
}

This code runs exactly the same query as your SQL – Jarek Tkaczyk Apr 27 '14 at 20:23 — Jarek Tkaczyk, Apr 27 '14 at 20:23

score 71 · Accepted Answer · answered Apr 28 '14 at 04:05

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'
    ]);

You could also specify to get all columns something like `customers.*` — Basheer Kharoti, Nov 22 '15 at 07:52
Aliases e.g. `customers.id as customer_id` are also possible for better readability. — tishma, Jun 24 '16 at 12:24

Haritsinh Gohil · Answer 2 · 2020-10-29T13:40:08.440

18

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();

edited Oct 29 '20 at 13:40

answered Jan 03 '20 at 06:50

Haritsinh Gohil

5,818
48
50

score 15 · Answer 3 · answered Aug 16 '14 at 11:20

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();

score 6 · Answer 4 · answered Apr 27 '14 at 20:18

6

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.

answered Apr 27 '14 at 20:18

Matthew Daly

9,212
2
42
83

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 Apr 27 '14 at 21:05
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? – eComEvo Apr 27 '14 at 21:32

score 0 · Answer 5 · answered Jul 14 '19 at 01:02

0

I would be using laravel whereDoesntHave to achieve this.

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

answered Jul 14 '19 at 01:02

Dushan

138
2
13

score 0 · Answer 6 · edited Feb 17 '21 at 17:24

0

use Illuminate\Database\Eloquent\Builder;

$query = Customers::with('orders');
$query = $query->whereHas('orders', function (Builder $query) use ($request) {
     $query = $query->where('orders.customer_id', 'NULL') 
});
    $query = $query->get();

edited Feb 17 '21 at 17:24

Atika

1,025
2
6
17

answered Sep 09 '20 at 11:37

Niaz Muhammad

51
4

Laravel Eloquent LEFT JOIN WHERE NULL

6 Answers6

Linked