Laravel 中的高级 whereNotNull 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27632915/
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
Advanced whereNotNull statement in Laravel
提问by chipit24
Is it possible to do the following in Laravel 4? ...
是否可以在 Laravel 4 中执行以下操作？...
DB::table('myTable')
->select(DB::raw($columnNames))
->whereNotNull(function($query) use($columns) {
foreach ($columns as $column) {
$query->whereNotNull($column);
}
})
->get();
If I have the following table:
如果我有下表：
table: myTable
id | name | age | weight
======================================
1 Jane NULL 150
2 NULL 12 80
3 Bob NULL NULL
4 John 22 120
5 Cody NULL NULL
If $columnsis [age, weight]and $columnNamesis 'age, weight', then applying the above whereNotNull statement, I would expect output like this:
如果$columnsis[age, weight]和$columnNamesis 'age, weight'，然后应用上面的 whereNotNull 语句，我希望输出如下：
age | weight
===================
NULL 150
12 80
22 120
How can I get this done?
我怎样才能做到这一点？
UPDATE:
更新：
The condition is to return all rows where the selected columns are not ALL null. So a whereNotNullclause must be applied to each (selected) column in each row. If all columns are NULL, then whereNotNull will return false and that row shouldn't be part of the results. So only rows which have AT LEAST one non-NULL value should be returned.
条件是返回所选列不全部为空的所有行。因此，whereNotNull必须将子句应用于每行中的每个（选定的）列。如果所有列都为 NULL，则 whereNotNull 将返回 false 并且该行不应成为结果的一部分。因此，只应返回具有至少一个非 NULL 值的行。
回答by lukasgeiter
If those are the only where's you don't even need a nested where. Important: orWhereNotNullinstead of whereNotNullso only one column has to be not NULL.
如果这些是唯一的位置，您甚至不需要嵌套的位置。重要提示：orWhereNotNull不是whereNotNull这样，只有一列必须是 not NULL。
$query = DB::table('myTable')->select(DB::raw($columnNames));
foreach($columns as $column){
$query->orWhereNotNull($column);
}
$result = $query->get();
Also (at least with your example) you don't need a separate variable $columnNamessince selectwill accept an array of column names.
另外（至少在您的示例中）您不需要单独的变量，$columnNames因为select它将接受列名数组。
$query = DB::table('myTable')->select($columns);
If you happen to need more where conditions (especially ones with AND) you need a nested where:
如果您碰巧需要更多 where 条件（尤其是带有 的条件AND），则需要一个嵌套的 where：
$query = DB::table('myTable')->select(DB::raw($columnNames));
$query->where(function($q) use ($columns){
foreach($columns as $column){
$q->orWhereNotNull($column);
}
});
$result = $query->get();
A nested where will put ()around the where clauses. That means instead of:
嵌套的 where 将放置()在 where 子句周围。这意味着而不是：
WHERE age IS NOT NULL OR weight IS NOT NULL AND foo = 'bar'
You get:
你得到：
WHERE (age IS NOT NULL OR weight IS NOT NULL) AND foo = 'bar'
回答by dailysleaze
Try using where() as the wrapping method. This would only show records that have both an age AND a weight.
尝试使用 where() 作为包装方法。这只会显示同时具有年龄和体重的记录。
DB::table('myTable')
->select(DB::raw($columnNames))
->where(function($query) use($columns) {
foreach ($columns as $column) {
$query->whereNotNull($column);
}
})
->get();
To show any records that have an age OR a weight, use orWhereNotNull() in the loop.
要显示任何具有年龄或体重的记录，请在循环中使用 orWhereNotNull()。
I see no reason why a loop would not work, because you are effectively doing this:
我看不出为什么循环不起作用，因为您正在有效地这样做：
$query = $query->whereNotNull('age');
$query = $query->whereNotNull('weight');
$results = $query->get();
$query = $query->whereNotNull('age');
$query = $query->whereNotNull('weight');
$results = $query->get();
