这是我的困境:我需要在网站的查询中使用$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;
}
看看这是否对您有帮助。