代码点火器 将 WHERE 子句注入到从模型中提取的现有查询中



这是我的困境:我需要在网站的查询中使用$this->request->uri->getSegments()的值作为WHERE子句。我知道$this->request->uri->getSegments()只能在控制器中访问,因此如果我从模型调用控制器中的查询,即。

控制器:

$brand = $this->request->uri->getSegment(1);
$model = new ShopModel();
data ['shop'] = $model->products()

型:

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

有没有办法->where('brand', $brand)子句注入控制器中的$model->products()

注意:我已经尝试了在控制器中构建所有查询(逐行(并按顺序添加WHERE语句的想法,但是我不断遇到错误,这将是一个"更整洁"的解决方案。

当像任何其他函数一样调用变量时,您可以将其作为函数中的参数传递。(参考资料(

控制器

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

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

编辑

如果要发送multiple parameters,可以逐个发送,也可以array发送,然后在model中,可以检查变量是否定义,像这样

multiple parameters-

控制器

$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

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

或作为array

控制器

$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

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

您还可以在model中使用foreach来防止代码重复 -

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

看看这是否对您有帮助。

相关内容

  • 没有找到相关文章

最新更新