Codeigniter Injecting WHERE clause into existing query pulled from Model

Question

Here's my dilemma: I need to use the values of $this->request->uri->getSegments() as WHERE clauses in my website's queries. I understand $this->request->uri->getSegments() can only be accessed in the Controller, so if I am calling my query in the Controller from the Model ie.

Controller:

$brand = $this->request->uri->getSegment(1);

$model = new ShopModel();
data ['shop'] = $model->products()

Model:

    public function products()
    {
        $query = $this  ->table('shop')
                        ->select('brand_name, brand_name_slug, count(*) as brand_name_total')
                        ->join('(SELECT sku, MIN(sale_price) as sale_price FROM shop GROUP BY sku) as min', 'shop.sku = min.sku and shop.sale_price = min.sale_price')
                        ->where('availability', 'in stock')
                        ->where('shop.sku !=', '')
                        ->groupBy('brand_name')
                        ->orderBy('brand_name')
                        ->findAll();

        return $query;
    }

Is there a way for me to inject ->where('brand', $brand) clause into $model->products() in the Controller?

Note: I've already trialled the idea of building all my queries IN the Controller (line by line) and adding the WHERE statement in order, however I kept getting bugs and this would be a 'tidier' solution.

Answer 1

You could just pass the variable as a parameter in the function when calling it like any other function. (Reference)

Controller

$brand = $this->request->uri->getSegment(1);

$model = new ShopModel();
data ['shop'] = $model->products($brand); // pass the variable

Model

public function products($brand){ // get the variable value 

    $query = $this  ->table('shop')
                    ->select('brand_name, brand_name_slug, count(*) as brand_name_total')
                    ->join('(SELECT sku, MIN(sale_price) as sale_price FROM shop GROUP BY sku) as min', 'shop.sku = min.sku and shop.sale_price = min.sale_price')
                    ->where('availability', 'in stock')
                    ->where('shop.sku !=', '')
                    ->where('brand', $brand) // use it in where clause
                    ->groupBy('brand_name')
                    ->orderBy('brand_name')
                    ->findAll();

    return $query;
}

Edit

If you want to send multiple parameters, you can either send them one by one or in an array, then in your model, you can check if the variable is defined or not, like so

By multiple parameters -

Controller

$brand = $this->request->uri->getSegment(1);
$xyz   = 'value';  // make sure to use key same as table column
$abc   = 'some-value';
$pqr   = 'some-other-value';

$model = new ShopModel();
data ['shop'] = $model->products($brand, $xyz, $abc, $pqr); // pass multiple variables

Model

public function products($brand = false, $xyz = false, $abc = false, $pqr = false){ // get variables value and give false default value

    $builder = $db->table('shop');

    $builder->select('brand_name, brand_name_slug, count(*) as brand_name_total');
    $builder->join('(SELECT sku, MIN(sale_price) as sale_price FROM shop GROUP BY sku) as min', 'shop.sku = min.sku and shop.sale_price = min.sale_price');
    $builder->where('availability', 'in stock');
    $builder->where('shop.sku !=', '');

    if($brand){ // if value is not false
        $builder->where('brand', $brand); // use it in where clause
    }
    if($xyz){ 
        $builder->where('xyz', $xyz);
    }
    if($abc){
        $builder->where('abc', $abc);
    }
    if($pqr){
        $builder->where('pqr', $pqr);
    }

    $builder->groupBy('brand_name')
    $builder->orderBy('brand_name')

    $query =  $builder->findAll();

    return $query;
}

or as an array

Controller

$arr['brand'] = $this->request->uri->getSegment(1);
$arr['xyz']   = 'value';  // make sure to use key same as table column
$arr['abc']   = 'some-value';
$arr['pqr']   = 'some-other-value';

$model = new ShopModel();
$data['shop'] = $model->products($arr); // pass the $arr array as parameter

Model

public function products($arr){ // get values in array

    $builder = $db->table('shop');

    $builder->select('brand_name, brand_name_slug, count(*) as brand_name_total');
    $builder->join('(SELECT sku, MIN(sale_price) as sale_price FROM shop GROUP BY sku) as min', 'shop.sku = min.sku and shop.sale_price = min.sale_price');
    $builder->where('availability', 'in stock');
    $builder->where('shop.sku !=', '');

    if(!empty($arr['brand']){ // if value is not false
        $builder->where('brand', $arr['brand']); // use it in where clause
    }
    if(!empty($arr['xyz']){
        $builder->where('xyz', $arr['xyz']);
    }
    if(!empty($arr['abc']){ 
        $builder->where('abc', $arr['abc']);
    }
    if(!empty($arr['pqr']){ 
        $builder->where('pqr', $arr['pqr']);
    }

    $builder->groupBy('brand_name')
    $builder->orderBy('brand_name')

    $query =  $builder->findAll();

    return $query;
}

You can also use foreach in your model to prevent repetition of your code -

Model

public function products($arr){ // get values in array

    $builder = $db->table('shop');

    $builder->select('brand_name, brand_name_slug, count(*) as brand_name_total');
    $builder->join('(SELECT sku, MIN(sale_price) as sale_price FROM shop GROUP BY sku) as min', 'shop.sku = min.sku and shop.sale_price = min.sale_price');
    $builder->where('availability', 'in stock');
    $builder->where('shop.sku !=', '');

    foreach($arr as $key => $val){
        if(!empty($val)){ // or if($val != "") -- if value is not false 
            $builder->where($key, $val); // use it in where clause
        }
    }        

    $builder->groupBy('brand_name')
    $builder->orderBy('brand_name')

    $query =  $builder->findAll();

    return $query;
}

See if this helps you.