yii Cdbcriteria无法正常工作

Question

I want to fetch data from two tables Store and mmm_store_services it's all conditions working fine except this

        if(isset($_POST['Store']['location']) && !empty($_POST['Store']['location'])){ 
        $conditions[] = ' AND t.location = '.$_POST["Store"]["location"];   
}

here is my all code

        $criteria=new CDbCriteria;
 
    $conditions = array();
    if(isset($_POST['Store']['category']) && !empty($_POST['Store']['category'])){
        $conditions[] = ' AND mmm_store_services.category_id ='.$_POST['Store']['category'];
}
 
    if(isset($_POST['Store']['sub_category']) && !empty($_POST['Store']['sub_category'])){
        $conditions[] = ' AND mmm_store_services.service_id ='.$_POST['Store']['sub_category'];
}
 
    if(isset($_POST['Store']['location']) && !empty($_POST['Store']['location'])){ 
        $conditions[] = ' AND t.location = '.$_POST["Store"]["location"];   
}
 
    if(isset($_POST['Store']['price']) && !empty($_POST['Store']['price'])){
        $price = explode('-',$_POST['Store']['price']);
        $minPrice = trim($price[0]);
        $maxPrice = trim($price[1]);
        $conditions[] = ' AND mmm_store_services.price between '.$minPrice.' AND '.$maxPrice;
}
 
 
    if(count($conditions)>0){
        $condition = implode(' ',$conditions);
        $criteria->join.='INNER JOIN mmm_store_services ON mmm_store_services.store_id = t.id '.$condition;
}
 
 
 
    $criteria->compare('t.approve','Y');     
    $model = new CActiveDataProvider('Store', array(
        'criteria'=>$criteria,
        'pagination'=>array('pageSize'=>'2'),
));

please give me a solution. thanks.

Answer 1

If your $conditions must be in WHERE part and not in JOIN part, than you are missing WHERE keyword and using CDbCriteria wrong (basically not using it at all).

$criteria = new CDbCriteria();
$flag = false;

if(isset($_POST['Store']['category']) && !empty($_POST['Store']['category'])){
    $criteria->addCondition(['mmm_store_services.category_id' => $_POST['Store']['category']]);
    $flag = true;
}

if(isset($_POST['Store']['sub_category']) && !empty($_POST['Store']['sub_category'])){
    $criteria->addCondition(['mmm_store_services.service_id' => $_POST['Store']['sub_category']]);
    $flag = true;
}

if(isset($_POST['Store']['location']) && !empty($_POST['Store']['location'])){ 
    $criteria->addCondition(['t.location' => $_POST["Store"]["location"]]);
    $flag = true;
}

if(isset($_POST['Store']['price']) && !empty($_POST['Store']['price'])){
    $price = explode('-',$_POST['Store']['price']);
    $minPrice = trim($price[0]);
    $maxPrice = trim($price[1]);
    $criteria->addBetweenCondition('mmm_store_services.price', $minPrice, $maxPrice);
    $flag = true;
}


if($flag){
    $criteria->with = ['mmm_store_service']; // Put `mmm_store_service` to relations of model 'Store'
    $criteria->together = true; // Check if you really need this parameter!
}

$criteria->compare('t.approve', 'Y');   

$model = new CActiveDataProvider('Store', array(
    'criteria' => $criteria,
    'pagination' => array('pageSize'=>'2'),
));