Coalesce Function for PHP

Coalesce function for PHP?

PHP 7 introduced a real coalesce operator:

echo $_GET['doesNotExist'] ?? 'fallback'; // prints 'fallback'

If the value before the ?? does not exists or is null the value after the ?? is taken.

The improvement over the mentioned ?: operator is, that the ?? also handles undefined variables without throwing an E_NOTICE.

Coalesce for arrays with PHP

array_merge is the function you're looking for. Precedence given to the last parametric array.

$allData = array_merge($_POST, $_GET, $arr);
return $allData['bar'];

CakePHP 3 - How to write COALESCE(...) in query builder?

I could not get Cake's coalesce() function to evaluate the parameters as fields, it was just returning the actual strings of the field names.

I got it working by manually creating the COALESCE statement instead.

// Create the query object first, so it can be used to create a SQL expression
$query = $this->Nodes->find();

// Modify the query
$query
    ->select([
        'id',
        'value' => $query->newExpr('COALESCE(Nodes.value, Parents.value)')
    ])
    ->where(['Nodes.id' => $id])
    ->contain('Parents')
    ->first();

Why does COALESCE order results in this way?

The COALESCE() function returns the first non-null argument that is received. So, if you go through each row and compare the parent/id column, you'll see that it is ordered like this:

7 (because parent is null)
2 (because parent is null)
2 (parent is not null, so it is used)
1 (because parent is null)
1 (parent is not null, so it is used)
1 (parent is not null, so it is used)

Which is in descending order, as you specified.

---

I suspect there may be some confusion here. So let me reiterate. COALESCE(parent, id) will return the first value out of those two that is not null. If parent is not null, it is returned. If it is null, it falls back on id and returns that. If you look at a list of those rows side by side and see the return values, it may be more clear:

| parent | id | return_value |
+--------+----+--------------+
|  null  | 7  |       7      |
|  null  | 2  |       2      |
|  2     | 4  |       2      |
|  null  | 1  |       1      |
|  1     | 3  |       1      |
|  1     | 5  |       1      |
|  1     | 6  |       1      |

---

Related Topics