Advanced whereNotNull statement in Laravel

Question

Is it possible to do the following in 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 $columns is [age, weight] and $columnNames is 'age, weight', then applying the above whereNotNull statement, I would expect output like this:

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 whereNotNull clause 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.

Disproof

So the condition is: at least one of the $columns can't be NULL? – Precisian

I have clarified the condition in my question, thanks! – Disproof

Answer 1

If those are the only where's you don't even need a nested where. Important: orWhereNotNull instead of whereNotNull so only one column has to be 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 $columnNames since select will accept an array of column names.

$query = DB::table('myTable')->select($columns);

---

If you happen to need more where conditions (especially ones with AND) you need a nested 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 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'

Precisian

Answer 2

Try using where() as the wrapping method. This would only show records that have both an age AND a weight.

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.

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

Birdiebirdlike