php Laravel Eloquent LEFT JOIN WHERE NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23328301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel Eloquent LEFT JOIN WHERE NULL
提问by eComEvo
I'm trying to use Eloquent to perform the following query during a database seed:
我正在尝试使用 Eloquent 在数据库种子期间执行以下查询：
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:
这是我在 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 emailand phonefields of the customerstable. I'm at a loss to explain this since the Customersand Ordersmodels are both artisan generated skeletons.
第一个查询总是返回完整的结果，而 Eloquent 等价的查询总是为除表的email和phone字段之外的所有内容返回空元素customers。我无法解释这一点，因为Customers和Orders模型都是工匠生成的骨架。
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):
这是当我 dd() 对种子（最初由 Faker 生成）第一个 Eloquent 查询时输出的数组：
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
}
回答by eComEvo
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'
]);
回答by Ioana Cucuruzan
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();
回答by Matthew Daly
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.
我会转储您的查询，以便您可以查看实际执行的 SQL，看看它与您所写的有何不同。
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.
希望这应该为您提供足够的信息，让您弄清楚出了什么问题。
回答by Haritsinh Gohil
Although above versions work well, i want to give you alternate short versionwhich i use very often:
尽管上述版本运行良好，但我想给您提供我经常使用的备用简短版本：
Customer::select('customers.*')
->leftJoin('orders', 'customers.id', '=', 'orders.customer_id')
->whereNull('orders.customer_id')->first();
回答by Dushan
I would be using laravel whereDoesntHaveto achieve this.
我会使用 laravel whereDoesntHave来实现这一点。
Customer::whereDoesntHave('orders')->get();
