Magento sql 查询判断是否为空(NULL)以及or的使用

在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))

Leave a comment

您的电子邮箱地址不会被公开。 必填项已用 * 标注