colleciton的启用
Ticket\Model\Log.php
Ticket\Model\Mysql4\Log.php
Ticket\Model\Mysql4\Log\Collection.php
<?php
class Test_Ticket_Model_Mysql4_Log_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{
public function _construct()
{
parent::_construct();
$this->_init('ticket/log');
}
public function getOrderTotalByState($state) {
$this->getSelect()
->reset('columns')
->where('customer_id=?', Mage::getSingleton('customer/session')->getCustomer()->getId())
->where('state=?', $state)
->columns(new Zend_Db_Expr('count(*) as total'));
// echo $this->getSelect();
return $this->fetchItem()->getData('total');
}
}
使用colleciton->getSelectSql()输出运行的SQL语句
<?php
$collection = Mage::getModel('catalog/category')->getCollection();
echo $collection->getSelectSql(true);
$collection->getSelectSql()->__toString();
?>
magento获取SQL语句的另外一种方法是设置打印SQL为true,collection全路径写法
$collection=Mage::getResourceModel('reports/product_collection');
$collection->printlogquery(true);
对模型对应的表集合进行排序
$collection = Mage::getModel('module/model_name')->getCollection();
$collection->getSelect()->order('last_name ASC');
对多个字段进行排序
$collection = Mage::getModel('module/model_name')->getCollection();
$collection->getSelect()->order( array('order ASC','last_name ASC', 'first_name ASC') );
3. 使用IF/THEN
$collection = Mage::getModel('module/model_name')->getCollection();
$collection->getSelect()->order( array('IF(`order`>0, `order`, 9999) ASC','last_name ASC', 'first_name ASC') );
在上面的例子中,如果order大于0并且小于等于9999按照升序排列,然后按last_name, first_name升序排列。
得到所有的记录明细非分页
$collection->toArray();
config.xml中的global标签 获取数据库配置信息
$dbname = (String) Mage::getConfig()->getNode('global/resources/default_setup/connection/dbname');
$host = (String) Mage::getConfig()->getNode('global/resources/default_setup/connection/host');
$user = (String) Mage::getConfig()->getNode('global/resources/default_setup/connection/username');
$pwd = (String) Mage::getConfig()->getNode('global/resources/default_setup/connection/password');
根据xml文件中的节点往下找就可以找到对应的字段
同理
<global>
<test>
<virtualcard>
<separator>-</separator>
<charset>
<alphanum>ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789</alphanum>
<alpha>ABCDEFGHIJKLMNOPQRSTUVWXYZ</alpha>
<num>0123456789</num>
</charset>
</virtualcard>
</test>
</global>
调用方法
const XML_CHARSET_SEPARATOR = 'global/test/virtualcard/separator';
public function getCodeSeparator()
{
return (string) Mage::app()->getConfig()->getNode(self::XML_CHARSET_SEPARATOR);
}
Join联表查询
$collection = Mage::getModel('ticket/ticket')->getCollection();
$collection->join('log','(main_table.id=log.ticketid and log.status>0)',array(''));//non select
if($status){
$collection->addFieldToFilter('log.status', $status);
}
$collection->addFieldToFilter('main_table.status', array("gt"=>0));
/*$collection->getSelect()->joinLeft
→joinInner() →joinLeft() →joinRight() →joinFull() →joinCross() →joinNatural()*/
$collection->getSelect()->joinLeft(array('o'=>'sales_flat_order'),'main_table.order_id = o.entity_id','grand_total');
分组
$collection->getSelect()->group('entity_id');
#$collection->groupByAttribute('entity_id');
分页
Mage::getModel('catalog/product')->getCollection()->addAttributeToSort('stdate','desc')
->setPageSize(10)->setCurPage(2);
$collection->getSelect()->limitPage(2,10);
只选某一个字段,点击Grid header可以排序
Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('price')->setOrder('id','desc');
在这里我将Magento addFieldToFilter 所有的条件判断符号列出来供大家参考。
array("eq"=>'n2610')
WHERE (e.sku = 'n2610')
//$collection->getSelect()->where('order_id =5');
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','ABC123')
WHERE (e.sku in ('n2610','ABC123'))
array("nin"=>array('n2610'))
WHERE (e.sku not in ('n2610'))
array("notnull"=>'n2610')
WHERE (e.sku is NOT NULL)
array("null"=>'n2610') //关键字NULL是最特殊的sql句,它将忽略你传入的值。
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'
addFieldToFilter 一个字段只能加一次,后面会替换前面的
if($parms['dt_start']&&empty($parms['dt_end'])){
$pointParams['created_at'] = array('gteq'=>trim($parms['dt_start']));
}
if($parms['dt_end']&&empty($parms['dt_start'])){
$pointParams['created_at'] = array('lteq'=>trim($parms['dt_end']));
}
if($parms['dt_end']&&$parms['dt_start']){
$pointParams['created_at'] = array('from'=>trim($parms['dt_start']),'to'=>trim($parms['dt_end']));
}
magento当使用group语句进行分页出现bug ,需修改文件 lib/Varien/Data/Collection/Db.php
public function getSelectCountSql()
{
$this->_renderFilters();
$countSelect = clone $this->getSelect();
$countSelect->reset(Zend_Db_Select::ORDER);
$countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
$countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
$countSelect->reset(Zend_Db_Select::COLUMNS);
// Count doesn't work with group by columns keep the group by
if(count($this->getSelect()->getPart(Zend_Db_Select::GROUP)) > 0) {
$countSelect->reset(Zend_Db_Select::GROUP);
$countSelect->distinct(true);
$group = $this->getSelect()->getPart(Zend_Db_Select::GROUP);
$countSelect->columns("COUNT(DISTINCT ".implode(", ", $group).")");
} else {
$countSelect->columns('COUNT(*)');
}
return $countSelect;
}
推荐在子类Collection中设置
$this->_totalRecords
dfghdfgd