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');
    }
}

実行中のSQL文をcolleciton->getSelectSql()で出力

<?php
$collection = Mage::getModel('catalog/category')->getCollection();
echo $collection->getSelectSql(true);
$collection->getSelectSql()->__toString();
?>

magento SQL文を取得するもう1つの方法は、印刷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);

フィールドを1つだけ選択し、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は1つのフィールドに1回しか追加できません.後で前のフィールドを置き換えます.

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文を使用してページング中にバグが発生した場合、ファイル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