We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Findfirst on null / not null value

I'm using Phalcon 3 and I have a problem with my query. I want to find if the row already exist.

    $colA = $data['columnA'] ?? null;
    $colB = $data['columnB'] ?? null;
    $colC = $data['columnC'] ?? null;

    $robot = Robots::findFirst(array(
        "columnA = :columnA: AND columnB = :columnB: AND columnC = :columnC:",
        "bind" => array("columnA" => $colA, "columnB" => $colB, "columnC" => $colC)
    ));

The problem is that if one of these variable is null I need to replace my conditon by IS NULL else my query will not work because the query will use colA = null.

Does Phalcon can resolve this problem ?



93.7k
Accepted
answer
edited Oct '18

I'm not 100% sure I understood the question, but something simple like?

$bind = [];
$conditions = [];

// Values
$collection = [
    'columnA' => $data['columnA'] ?? null,
    'columnB' => $data['columnB'] ?? null,
    'columnC' => $data['columnC'] ?? null,
];

// Create conditions
foreach ($collection as $column => $value) {
    if (is_null($value)) {
        $conditions[] = $column . ' IS NULL';
    } else {
        $conditions[] = $column . ' = :' . $column . ':';
        $bind[$column] = $value;
    }   
}

// Query
$robot = Robots::findFirst(array(
    'conditions' => implode(' AND ', $conditions),
    'bind' => $bind
));

Yep it's that. I aksed if there is something in the ORM who can skip this step :

foreach ($collection as $column => $value) {
    if (is_null($value)) {
        $conditions[] = $column . ' IS NULL';
    } else {
        $conditions[] = $column . ' = :' . $column . ':';
        $bind[$column] = $value;
    }   
}

I just want to know if Phalcon can help to skip that. For example if I do : findFirst('colA = ' . null) it will replace automatically = by is

Sadly, not :(

Ok ^^ thanks