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
时间: 2024-07-29 05:54:37