Magento SQL绑定查询条件

Magento从数据库中取数据时大多用的Collection,Collection的基础知识估计大家都了解.

1.基于collection可以连接其他表，方法一般为：

$collection->getSelect()->join()

 

2.给collection添加筛选条件

(1).addAttributeToFilter();

(2).addFieldToFilter();

这里重点讲第二个,各种筛选简单如下：

$collection->addFieldToFilter('fieldName',array('eq'=>'n2610'));
$collection->addFieldToFilter('fieldName',array('neq'=>'n2610'));
$collection->addFieldToFilter('fieldName',array('in'=>array('n2610','n2611')));
$collection->addFieldToFilter('fieldName',array('null'=>'true')); //为空
$collection->addFieldToFilter('fieldName',array('notnull'=>'true')); //不为空
$collection->addFieldToFilter('fieldName',array('from'=>'2012-04-17','to'=>'2012-04-29')); //取区间
..............
 array("eq"=>'n2610')
WHERE (e.sku = 'n2610')

array("neq"=>'n2610')
WHERE (e.sku != 'n2610')

array("like"=>'n2610')
WHERE (e.sku like 'n2610')

array("nlike"=>'n2610')
WHERE (e.sku not like 'n2610')

array("is"=>'n2610')
WHERE (e.sku is 'n2610')

array("in"=>array('n2610'))
WHERE (e.sku in ('n2610'))

array("nin"=>array('n2610'))
WHERE (e.sku not in ('n2610'))

array("notnull"=>'n2610')
WHERE (e.sku is NOT NULL)

array("null"=>'n2610')
WHERE (e.sku is NULL)

array("gt"=>'n2610')
WHERE (e.sku > 'n2610')

array("lt"=>'n2610')
WHERE (e.sku < 'n2610')

array("gteq"=>'n2610')
WHERE (e.sku >= 'n2610')

array("moreq"=>'n2610') 
WHERE (e.sku >= 'n2610')

array("lteq"=>'n2610')
WHERE (e.sku <= 'n2610')

array("finset"=>array('n2610'))
WHERE (find_in_set('n2610',e.sku))

array('from'=>'10','to'=>'20')
WHERE e.sku >= '10' and e.sku <= '20' 

 

关于上面使用的方式，内部实现方法如下

array(
            'eq'            => "{{fieldName}} = ?",
            'neq'           => "{{fieldName}} != ?",
            'like'          => "{{fieldName}} LIKE ?",
            'nlike'         => "{{fieldName}} NOT LIKE ?",
            'in'            => "{{fieldName}} IN(?)",
            'nin'           => "{{fieldName}} NOT IN(?)",
            'is'            => "{{fieldName}} IS ?",
            'notnull'       => "{{fieldName}} IS NOT NULL",
            'null'          => "{{fieldName}} IS NULL",
            'gt'            => "{{fieldName}} > ?",
            'lt'            => "{{fieldName}} < ?",
            'gteq'          => "{{fieldName}} >= ?",
            'lteq'          => "{{fieldName}} <= ?",
            'finset'        => "FIND_IN_SET(?, {{fieldName}})",
            'regexp'        => "{{fieldName}} REGEXP ?",
            'from'          => "{{fieldName}} >= ?",
            'to'            => "{{fieldName}} <= ?",
            'seq'           => null,
            'sneq'          => null
        );

 更多Magento技术文章或Magento扩展请参看http://www.iifire.com