使用PhpActiveRecord检查NULL值

Question

According to the documentation here (http://www.phpactiverecord.org/projects/main/wiki/Finders)

There is a way to find records in the database like so below.

 # fetch all lousy romance novels which are cheap
   Book::all(array('conditions' => array('genre = ? AND price < ?', 'Romance', 15.00)));
 # sql => SELECT * FROM `books` WHERE genre = 'Romance' AND price < 15.00

This however will not work if any of the values are NULL. This is simply because NULL is not any value so it does not have anything to compare from. This I understand, but what I can't figure out in the documentation is how to actually check using that format if the value is null or not.

In SQL you could simply say WHERE value is null, or not null, but with PHPActiveRecord condition array string I'm not sure...

The reason I want to do it with the condition string and array is I have code setup which automatically creates those conditions, I'll post the code below.

    function create_find_options($fields,$operators,$values,$sortfields,$sortdirections,$limit,$offset,$logic){
        $conditionstring = '';
        $fieldcount = count($fields);
        $i=0;

        for($k=0;$k<count($logic)-1;$k++){
            $conditionstring.="(";//add starting parenthesis for every known logic.
        }

        for($i=0;$i<$fieldcount;$i++){
            $conditionstring.=$fields[$i];
            switch($operators[$i]){
                case "equals":
                    $conditionstring.=" = ?";
                    break;
                case "greaterthan":
                    $conditionstring.=" > ?";
                    break;
                case "lessthan":
                    $conditionstring.=" < ?";
                    break;
                case "notequals":
                    $conditionstring.=" != ?";
                    break;
                case "contains":
                    $conditionstring.=" LIKE ?";
                    break;
            }

            if($i!=$fieldcount-1 && $fieldcount>=2){
                if($i>0){
                    $conditionstring.=")";//first condition does not get ending parenthesis.
                }
                $conditionstring.=" ".$logic[$i]." ";//AND or OR
            }
        }

        //$conditionstring = substr($conditionstring,0,strlen($conditionstring)-5);
        //die($conditionstring);

        $options = array('conditions' => array($conditionstring));

        $i=0;
        for($i=0;$i<$fieldcount;$i++){
            if($operators[$i]=="contains"){ //exception for contains because it needs the percentage symbols around the value.
                $options['conditions'][] = "%".$values[$i]."%";
            }else{
                $options['conditions'][] = $values[$i];
            }
        }

        //Add any sorts now.
        $i=0;
        $sortcount = count($sortfields);
        $orderstring = '';
        for($i=0;$i<$sortcount;$i++){
            $orderstring.= $sortfields[$i]." ".$sortdirections[$i].",";
        }
        $orderstring = rtrim($orderstring,",");//remove trailing comma

        $options['order'] = $orderstring;//sets order rules.

        //Add any limits now.
        if(isset($limit)){
            $options['limit']   = $limit;
        }
        if(isset($offset)){
            $options['offset']  = $offset;
        }

        return $options;
    }

So my function will automatically create the condition string needed, but it fails on NULL for the reason I described above. I think I need to add some extra conditions in here that if I detect NULL how to handle it better, but I'm not sure how to do that or if that's possible with PHPActiveRecord?

Answer 1

Well after spending ... all day, I have found a solution. I suppose it might have been common sense but it was not in the documentation so I had to guess.

Apparently you can simply say 'is null' in the condition string like regular SQL and it will work...

That said I updated my function to the following and this generate a complete options array with the conditions ready to go that will work even with null values.

Hopefully this is useful to someone! In my situation I wanted 0 to be the same as null, so you can adjust accordingly for your situation.

function create_find_options($fields,$operators,$values,$sortfields,$sortdirections,$limit,$offset,$logic){
    $conditionstring = '';
    $fieldcount = count($fields);
    $i=0;

    for($k=0;$k<count($logic)-1;$k++){
        $conditionstring.="(";//add starting parenthesis for every known logic.
    }

    for($i=0;$i<$fieldcount;$i++){
        $conditionstring.=$fields[$i];
        $nullFound = false;
        if($values[$i]=='0'){
            $nullFound = true;
        }
        switch($operators[$i]){
            case "equals":
                if($nullFound==true){
                    $conditionstring.=" is null OR ".$fields[$i].' = 0';
                }else{
                    $conditionstring.=" = ?";
                }
                break;
            case "greaterthan":
                $conditionstring.=" > ?";
                break;
            case "lessthan":
                $conditionstring.=" < ?";
                break;
            case "notequals":
                if($nullFound==true){
                    $conditionstring.=" is not null OR ".$fields[$i].' != 0';
                }else{
                    $conditionstring.=" != ? OR ".$fields[$i].' is null';
                }
                break;
            case "contains":
                $conditionstring.=" LIKE ?";
                break;
        }

        if($i!=$fieldcount-1 && $fieldcount>=2){
            if($i>0){
                $conditionstring.=")";//first condition does not get ending parenthesis.
            }
            $conditionstring.=" ".$logic[$i]." ";//AND or OR
        }
    }

    //$conditionstring = substr($conditionstring,0,strlen($conditionstring)-5);
    //die($conditionstring);

    $options = array('conditions' => array($conditionstring));

    $i=0;
    for($i=0;$i<$fieldcount;$i++){
        if($values[$i]!="0"){
            if($operators[$i]=="contains"){ //exception for contains because it needs the percentage symbols around the value.
                $options['conditions'][] = "%".$values[$i]."%";
            }else{
                $options['conditions'][] = $values[$i];
            }
        }
    }

    //Add any sorts now.
    $i=0;
    $sortcount = count($sortfields);
    $orderstring = '';
    for($i=0;$i<$sortcount;$i++){
        $orderstring.= $sortfields[$i]." ".$sortdirections[$i].",";
    }
    $orderstring = rtrim($orderstring,",");//remove trailing comma

    $options['order'] = $orderstring;//sets order rules.

    //Add any limits now.
    if(isset($limit)){
        $options['limit']   = $limit;
    }
    if(isset($offset)){
        $options['offset']  = $offset;
    }
    //die(print_r($options));
    return $options;
}