在Magento 的sql语句中判断条件某个字段的值是否为空,可以使用下面的sql语句。
1 . 判断字段 transaction_id 的值为空(NULL);
关键sql代码:
addFieldToFilter('transaction_id', array('null' => 'transaction_id'))
下面的条件是 判断order_id =70519 并且 transaction_id 为空的数据
$order_id = 70519;
$creditmemoList = Mage::getModel('sales/order_creditmemo')
->getCollection()
->addFieldToFilter('order_id', $order_id)
->addFieldToFilter('transaction_id', array('null' => 'transaction_id'));
echo (string)$creditmemoList->getSelect();
//输出结果:
SELECT `main_table`.* FROM `sales_flat_creditmemo` AS `main_table` WHERE (`order_id` = '70519') AND (`transaction_id` IS NULL)
2. 判断字段 transaction_id 的值不为空(NULL):
关键sql代码:
addFieldToFilter('transaction_id', array('notnull' => 'transaction_id'));
下面的条件是 判断order_id =70519 并且 transaction_id 不为空的数据
$order_id = 70519;
$creditmemoList = Mage::getModel('sales/order_creditmemo')
->getCollection()
->addFieldToFilter('order_id', $order_id)
->addFieldToFilter('transaction_id', array('notnull' => 'transaction_id'));
echo (string)$creditmemoList->getSelect();
//输出结果:
SELECT `main_table`.* FROM `sales_flat_creditmemo` AS `main_table` WHERE (`order_id` = '70519') AND (`transaction_id` IS NOT NULL)
3. Magento判断多个字段不同时(or),不为空的情况即使用:
关键sql代码:
->addFieldToFilter(
array('base_am_amount_total_refunded','am_amount_total_refunded','base_amstcred_amount','amstcred_amount'),
array(
array('notnull' =>'base_am_amount_total_refunded'),
array('notnull' =>'am_amount_total_refunded'),
array('notnull' =>'base_amstcred_amount'),
array('notnull' =>'amstcred_amount')
)
);
下面是判断这4个字段 base_am_amount_total_refunded 或者 am_amount_total_refunded 或者 base_amstcred_amount 或者 amstcred_amount ,任意一个字段不为空的情况。
$order_id = 70519;
$collection = Mage::getModel('sales/order_creditmemo')
->getCollection()
->addFieldToFilter('order_id', $order_id)
->addFieldToFilter(
array('base_am_amount_total_refunded','am_amount_total_refunded','base_amstcred_amount','amstcred_amount'),
array(
array('notnull' =>'base_am_amount_total_refunded'),
array('notnull' =>'am_amount_total_refunded'),
array('notnull' =>'base_amstcred_amount'),
array('notnull' =>'amstcred_amount')
)
);
echo (string)$creditmemoList->getSelect();
//输出结果:
SELECT `main_table`.* FROM `sales_flat_creditmemo` AS `main_table`
WHERE (`order_id` = '70519') AND
((`base_am_amount_total_refunded` IS NOT NULL)
OR (`am_amount_total_refunded` IS NOT NULL)
OR (`base_amstcred_amount` IS NOT NULL) OR
(`amstcred_amount` IS NOT NULL))