Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Order by created_at in product collection not working
 
Bright Ideas
Sr. Member
 
Total Posts:  140
Joined:  2011-04-17
Mumbai
 

Guys,
I have the following code.

$collection $category->getProductCollection()
                                  ->
addFieldToFilter('type_id', array('configurable'))
                                  ->
addAttributeToSelect("*")
                                   ->
addMinimalPrice()
                                   ->
addTierPriceData()
                                   ->
addFinalPrice()
                                 ->
addAttributeToSort('created_at''DESC')                                  
                                  ->
setPageSize(8);

I am getting the product collection but not in the declared order. i.e. by ‘created_at’. But when I remove ‘->addFinalPrice()’ I have the products in particular order by ‘created_at’.

Please please help me.

 
Magento Community Magento Community
Magento Community
Magento Community
 
TreInnova
Guru
 
Avatar
Total Posts:  465
Joined:  2009-02-20
Fano
 
Bright Ideas - 23 April 2013 08:37 PM

Guys,
I have the following code.

$collection $category->getProductCollection()
                                  ->
addFieldToFilter('type_id', array('configurable'))
                                  ->
addAttributeToSelect("*")
                                   ->
addMinimalPrice()
                                   ->
addTierPriceData()
                                   ->
addFinalPrice()
                                 ->
addAttributeToSort('created_at''DESC')                                  
                                  ->
setPageSize(8);

I am getting the product collection but not in the declared order. i.e. by ‘created_at’. But when I remove ‘->addFinalPrice()’ I have the products in particular order by ‘created_at’.

Please please help me.

Hi the method addFinalPrice() call a method addPriceData()

public function addPriceData($customerGroupId null$websiteId null)
    
{
        $this
->_productLimitationFilters['use_price_index'true;

        if (!isset(
$this->_productLimitationFilters['customer_group_id']) && is_null($customerGroupId)) {
            $customerGroupId 
Mage::getSingleton('customer/session')->getCustomerGroupId();
        
}
        
if (!isset($this->_productLimitationFilters['website_id']) && is_null($websiteId)) {
            $websiteId       
Mage::app()->getStore($this->getStoreId())->getWebsiteId();
        
}

        
if (!is_null($customerGroupId)) {
            $this
->_productLimitationFilters['customer_group_id'$customerGroupId;
        
}
        
if (!is_null($websiteId)) {
            $this
->_productLimitationFilters['website_id'$websiteId;
        
}

        $this
->_applyProductLimitations();

        return 
$this;
    
}
and then the _applyProductLimitations() method
protected function _applyProductLimitations()
    
{
        $this
->_prepareProductLimitationFilters();
        
$this->_productLimitationJoinWebsite();
        
$this->_productLimitationJoinPrice();
        
$filters $this->_productLimitationFilters;

        if (!isset(
$filters['category_id']) && !isset($filters['visibility'])) {
            
return $this;
        
}

        $conditions 
= array(
            
'cat_index.product_id=e.entity_id',
            
$this->getConnection()->quoteInto('cat_index.store_id=?'$filters['store_id'])
        );
        if (isset(
$filters['visibility']) && !isset($filters['store_table'])) {
            $conditions[] 
$this->getConnection()
                ->
quoteInto('cat_index.visibility IN(?)'$filters['visibility']);
        
}
        $conditions[] 
$this->getConnection()
            ->
quoteInto('cat_index.category_id=?'$filters['category_id']);
        if (isset(
$filters['category_is_anchor'])) {
            $conditions[] 
$this->getConnection()
                ->
quoteInto('cat_index.is_parent=?'$filters['category_is_anchor']);
        
}

        $joinCond 
join(' AND '$conditions);
        
$fromPart $this->getSelect()->getPart(Zend_Db_Select::FROM);
        if (isset(
$fromPart['cat_index'])) {
            $fromPart[
'cat_index']['joinCondition'$joinCond;
            
$this->getSelect()->setPart(Zend_Db_Select::FROM$fromPart);
        
}
        
else {
            $this
->getSelect()->join(
                array(
'cat_index' => $this->getTable('catalog/category_product_index')),
                
$joinCond,
                array(
'cat_index_position' => 'position')
            );
        
}

        $this
->_productLimitationJoinStore();

        
Mage::dispatchEvent('catalog_product_collection_apply_limitations_after', array(
            
'collection'    => $this
        
));

        return 
$this;
    
}
You can print the collection select query with or without the addFinalPrice or investigate in limitation query.
Regards
Filippo
 
Magento Community Magento Community
Magento Community
Magento Community
 
Bright Ideas
Sr. Member
 
Total Posts:  140
Joined:  2011-04-17
Mumbai
 

Thanks very much Fillipo,

Really Sorry for the mistake in my question. When I remove ‘addTierPriceData’ not ‘addFinalPrice’ then it creates problem.  When I echo the sql query with getSelect, it displays perfect means it gives the proper data. But When I loop though collection then it is givving me the products not in the order I want.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Bright Ideas
Sr. Member
 
Total Posts:  140
Joined:  2011-04-17
Mumbai
 

Anyone here ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
TreInnova
Guru
 
Avatar
Total Posts:  465
Joined:  2009-02-20
Fano
 
Bright Ideas - 25 April 2013 10:46 PM

Anyone here ?

Hi can you give us the generated query?
regards
filippo

 
Magento Community Magento Community
Magento Community
Magento Community
 
Bright Ideas
Sr. Member
 
Total Posts:  140
Joined:  2011-04-17
Mumbai
 

Thanks.

This is the query,

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULLLEAST(price_index.min_priceprice_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_priceFROM `catalog_product_entity` AS `eINNER JOIN `catalog_category_product_index` AS `cat_indexON cat_index.product_id=e.entity_id AND cat_index.store_id=AND cat_index.category_id='5' AND cat_index.is_parent=1 INNER JOIN `catalog_product_index_price` AS `price_indexON price_index.entity_id e.entity_id AND price_index.website_id '1' AND price_index.customer_group_id 0 WHERE (((`e`.`type_id` = 'configurable'))) ORDER BY `e`.`created_atDESC

And the code,

$collection $category->getProductCollection()->addFieldToFilter('type_id', array('configurable'))->addAttributeToSelect("*")->addMinimalPrice()->addTierPriceData()
    ->
addAttributeToSort('created_at''DESC')->addFinalPrice()->setPageSize(8);    
    
$cateid=$category->getId();
    
$products=array();
    foreach (
$collection as $product{
        $products[]
=$product;
    
}

When I print $product array, I get product in ascending order

 
Magento Community Magento Community
Magento Community
Magento Community
 
TreInnova
Guru
 
Avatar
Total Posts:  465
Joined:  2009-02-20
Fano
 
Bright Ideas - 28 April 2013 08:54 PM

Thanks.

This is the query,

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULLLEAST(price_index.min_priceprice_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_priceFROM `catalog_product_entity` AS `eINNER JOIN `catalog_category_product_index` AS `cat_indexON cat_index.product_id=e.entity_id AND cat_index.store_id=AND cat_index.category_id='5' AND cat_index.is_parent=1 INNER JOIN `catalog_product_index_price` AS `price_indexON price_index.entity_id e.entity_id AND price_index.website_id '1' AND price_index.customer_group_id 0 WHERE (((`e`.`type_id` = 'configurable'))) ORDER BY `e`.`created_atDESC

And the code,
$collection $category->getProductCollection()->addFieldToFilter('type_id', array('configurable'))->addAttributeToSelect("*")->addMinimalPrice()->addTierPriceData()
    ->
addAttributeToSort('created_at''DESC')->addFinalPrice()->setPageSize(8);    
    
$cateid=$category->getId();
    
$products=array();
    foreach (
$collection as $product{
        $products[]
=$product;
    
}

When I print $product array, I get product in ascending order

Hi i think the code is correct.
In the last part of your code you creare an array of product object.

$products=array();
    foreach (
$collection as $product{
        $products[]
=$product;
then you print the $products array.
Are you sure the order is incorrect?
foreach ($collection as $product{
         Mage
::log($product->getSku());
       
}
In which order do you see the products sku?
Regards
Filippo
 
Magento Community Magento Community
Magento Community
Magento Community
 
Bright Ideas
Sr. Member
 
Total Posts:  140
Joined:  2011-04-17
Mumbai
 

Same ascending order :(

 
Magento Community Magento Community
Magento Community
Magento Community
 
Bright Ideas
Sr. Member
 
Total Posts:  140
Joined:  2011-04-17
Mumbai
 

In the Collection.php file,

public function addTierPriceData()
    
{
        
if ($this->getFlag('tier_price_added')) {
            
return $this;
        
}

        $tierPrices 
= array();
        
$productIds = array();
        foreach (
$this->getItems() as $item{
            $productIds[] 
$item->getId();
            
$tierPrices[$item->getId()= array();
        
}
        
if (!$productIds{
            
return $this;
        
}

The loop

foreach ($this->getItems() as $item{
is creating some issues.
 
Magento Community Magento Community
Magento Community
Magento Community
 
Bright Ideas
Sr. Member
 
Total Posts:  140
Joined:  2011-04-17
Mumbai
 

Any1 here who can help?

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top