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
LIMIT 1
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();
These queries are identical when output raw, however, the Eloquent equivalent always returns empty elements for everything but the email
and phone
fields of the customers
table.
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
}
The only apparent cause of this is that the NULL fields from the orders table row that has yet to be created are overwriting the row values from 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 from an SQL query and it performs as expected in MySQL Workbench. How do I prevent Laravel's Eloquent from doing this?
You need to manually select and alias columns. Eloquent doesn't know which columns to default to so it takes the joined table by default.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community