magento model collection

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