使用PhpActiveRecord检查空值

Question

根据这里的文档(http://www.phpactiverecord.org/projects/main/wiki/Finders)

有一种方法可以像下面这样在数据库中找到记录。

 # 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

但是，如果其中任何一个值为NULL，则此操作将无效。这只是因为NULL不是任何值，所以它没有任何可比较的地方。我理解这一点，但在文档中我无法理解的是，如果值是否为空，如何使用该格式进行实际检查。

在SQL中，您可以简单地说WHERE值为空，或不为空，但是对于PHPActiveRecord条件数组字符串，我不确定.

我之所以想用条件字符串和数组来做这件事，是因为我有自动创建这些条件的代码设置，我将在下面发布代码。

    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;
    }

因此，我的函数将自动创建所需的条件字符串，但由于上述原因，它在NULL上失败。我想我需要在这里添加一些额外的条件，如果我检测到NULL，如何更好地处理它，但我不知道如何做到这一点，或者这在PHPActiveRecord中是否可行

Answer 1

在消费之后..。一整天，我都找到了解决办法。我想这可能是常识，但不是在文档中，所以我不得不猜测。

显然，您可以简单地在条件字符串中说'is‘，就像常规SQL，它会工作.

尽管如此，我还是将函数更新为以下内容，这将生成一个完整的选项数组，该数组的条件已经就绪，即使使用空值也可以工作。

希望这对某人有用！在我的情况下，我希望0与null相同，因此您可以根据您的情况进行相应的调整。

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;
}