使用CakePHP查询时得到错误的结果 [英] Getting Wrong result when using CakePHP Query
问题描述
我正在尝试获取低库存>库存的产品.
I am trying to fetch Product where Low stock > Stock.
为什么我在CakePHP查询中得到错误的结果?
Why i am getting Wrong results in CakePHP Query ?
使用CakePHP自定义查询可以正常工作.但是使用默认分页查询结果是错误的.
Using CakePHP Custom query it is working fine. but with default pagination query result are wrong.
我在产品和类别模型之间有关联.
I have association between Product and Category Model.
控制器代码:
$condition = array('Product.status =' => 1, 'Product.is_deleted !=' => 1,
'Product.low_stock_alert != ' => 0, 'Product.low_stock_alert >' => 'Product.stock');
$this->paginate = array('fields' => array('Product.id', 'Product.name',
'Product.code', 'Product.stock_type','Product.low_stock_alert',
'Product.stock','category.name'),'conditions' => $condition, 'limit' => Configure::read('LIST_PROD_NUM_RECORDS'),'order' => 'Product.id ASC');
正在生成查询:
SELECT `Product`.`id`, `Product`.`name`, `Product`.`code`, `Product`.`stock_type`,
`Product`.`stock`, `category`.`name` FROM `shopping`.`products` AS `Product` LEFT
JOIN `shopping`.`categories` AS `Category` ON (`Product`.`category_id` =
`Category`.`id`) LEFT JOIN `shopping`.`users` AS `AddedBy` ON (`Product`.`added_by`
= `AddedBy`.`id`) LEFT JOIN `shopping`.`users` AS `ModifiedBy` ON
(`Product`.`modified_by` = `ModifiedBy`.`id`) WHERE `Product`.`status` = 1 AND
`Product`.`is_deleted` != 1 AND `Product`.`low_stock_alert` != 0 AND
`Product`.`low_stock_alert` > 'Product.stock' ORDER BY `Product`.`id` ASC LIMIT 100
模型-> Product.php
class Product extends AppModel {
public $name = 'Product';
public $cacheQueries = false;
public $actsAs = array('Containable');
public $belongsTo = array(
'Category' => array(
'className' => 'Category',
'foreignKey' => 'category_id',
'fields' => array('name', 'description', 'stock_type', 'qrt_per', 'half_per', 'three_forth_per')
),
'AddedBy' => array(
'className' => 'User',
'foreignKey' => 'added_by',
'fields' => array('first_name', 'last_name', 'email')
),
'ModifiedBy' => array(
'className' => 'User',
'foreignKey' => 'modified_by',
'fields' => array('first_name', 'last_name', 'email')
)
);
}
模型-> Category.php
class Category extends AppModel {
var $name = 'Category';
public $cacheQueries = false;
public $belongsTo = array(
'AddedBy' => array(
'className' => 'User',
'foreignKey' => 'added_by',
'fields' => array('first_name', 'last_name', 'email')
),
'ModifiedBy' => array(
'className' => 'User',
'foreignKey' => 'modified_by',
'fields' => array('first_name', 'last_name', 'email')
)
);
public $hasMany = array(
'Product' => array(
'className' => 'Product',
'order' => 'Product.created DESC'
)
);
}
错误的结果:
但是在自定义查询"下面,我得到了准确的结果:
But with this below Custom Query i am getting accurate result:
$data = $this->Product->Query("SELECT products.*, categories.name, categories.description, categories.stock_type, categories.qrt_per, categories.half_per, categories.three_forth_per, AddedBy.first_name, AddedBy.last_name, AddedBy.email, ModifiedBy.first_name, ModifiedBy.last_name, ModifiedBy.email FROM sunnaair_kaziDB.products AS products LEFT JOIN sunnaair_kaziDB.categories AS categories ON (products.category_id = categories.id) LEFT JOIN sunnaair_kaziDB.users AS AddedBy ON (products.added_by = AddedBy.id) LEFT JOIN sunnaair_kaziDB.users AS ModifiedBy ON (products.modified_by = ModifiedBy.id) WHERE products.status = 1 AND products.is_deleted != 1 AND products.low_stock_alert != 0 AND products.low_stock_alert > products.stock ORDER BY products.id ASC LIMIT 50");
推荐答案
您的问题出在这种情况下:
Your problem is with this condition:
'Product.low_stock_alert >' => 'Product.stock'
在Cake中," Product.stock
"不是作为表名而是作为文字字符串读取.请查看生成的SQL中的细微错误:
In Cake, 'Product.stock
' is not read as a table name but as a literal string. Please see the subtle error in the generated SQL:
AND `Product`.`low_stock_alert` > 'Product.stock'
要解决此问题,请尝试将条件更改为:
To fix it, please try changing the condition to:
'Product.low_stock_alert > Product.stock'
因此整行变为:
$condition = array('Product.status =' => 1, 'Product.is_deleted !=' => 1,
'Product.low_stock_alert != ' => 0, 'Product.low_stock_alert > Product.stock');
请记住以下内容: =>
用于转义.将列与列进行比较时,请勿使用此选项.对于这些情况,您甚至可以一起使用 =>
进行转储,因为这里没有用于SQL注入攻击的向量.
Remember the following: =>
is for escaping. Don't use this when comparing columns to columns. You could even just dump using =>
all together for these conditions as there is no vector for an SQL injection attack here.
这篇关于使用CakePHP查询时得到错误的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!