Magento – Wrong sort order in category product collection

category-productsenterprise-1.13position;product-collection

I am trying to get the product collection based on the position (defined in Category Products in admin) in the category, but it doesn't work with product collection object whereas works correctly with product collection array (getData())

E.g. in Admin > Manage Categories > Category > Category Products 
(Is Anchor = true but no sub-categories)
Product | Position
Prod A  | 1
Prod B  | 2

When I loop through product collection $_productCollection in catalog/product/list.phtml file, the product's order comes Prod B and then Prod A, whereas if I use $_productCollection->getData() and loop through it, it gives products in correct order.

foreach($_productCollection as $_product) //gives Prod B and then Prod A, which is not expected

foreach($_productCollection->getData() as $_product) //gives Prod A and then Prod B, which is correct

Any ideas why it would behave differently when accessing it as an object and as an array, on the same collection?

Thanks!

Best Answer

Looking at the files which are relevant for this, I can identify Varien_Data_Collection which is the base class and implements IteratorAggregate, so you can loop over collections using foreach($_productCollection as $product).

It does so via the method getIterator() which mainly returns new ArrayIterator($this->_items);

In contrast, a level further up extending from Varien_Data_Collection there is the class Varien_Data_Collection_Db.

This class implements the method getData() as follows:

public function getData()
{
    if ($this->_data === null) {
        $this->_renderFilters()
             ->_renderOrders()
             ->_renderLimit();
        $this->_data = $this->_fetchAll($this->_select);
        $this->_afterLoadData();
    }
    return $this->_data;
}

It explicitly uses the _renderOrders() method which adds an order clause to the select statement. This is why getData() will use your given order, but the default iteration will not.

Related Solutions

Magento – How to get first item from product collection

To get the first item in a collection simply use the getFirstItem() function on the collection.

Example:

// this gets all the products
$productCollection = Mage::getResourceModel('catalog/products_collection');
// this line gets just the first product
$firstItem = $productCollection->getFirstItem();

Example 2 (for this particular question):

$_category  = $this->getCurrentCategory();
$_collection = $_category->getProductCollection();
$_product =  $_collection->getFirstItem(); // this will get first item in collection

Some other areas this can be used:

Customers

$customerCollection = Mage::getResourceModel('customer/customer_collection');
$firstCustomer = $customerCollection->getFirstItem();

Orders

$orderCollection = Mage::getResourceModel('sales/order_collection');
$firstOrder = $orderCollection->getFirstItem();

Please Note:

Its not a good idea to load ALL the products/customers/orders for this takes a lot of resources. The preferred way is to limit what you want to load by using the addAttributeToFilter() or addFieldToFilter() functions, see example below:

$productCollection = Mage::getResourceModel('catalog/product_collection')
                           ->addAttributeToFilter('sku', 'book123`);

Product – Programmatically Assign Product’s Categories and Sort Order/Position

You can do that writing directly to the database below is a working code that do that using an array of categories products

You have to create the array like this

            $categoryPosition[] = array('product_id' => $mageProduct->getId(),
                                        'category_id' => $categoryId,
                                        'position' => $position);

an run this code

foreach ($categoryPosition as $position){                        
            //print "Position: c=".$position['category_id']." prod=".$position['product_id']." pos=".$position['position'] . "\n";
            $query = "UPDATE tn_catalog_category_product SET position = :position "
                    . "WHERE category_id = :category_id and product_id = :product_id";
            $values = array('position' => $position['position'],
                                'category_id' => $position['category_id'],
                                'product_id' => $position['product_id']);
            $writeConnection->query($query,$values);                    
}

and remember to run the indexer catalog_category_product

Thanks

Best Answer

Related Solutions

Magento – How to get first item from product collection

Product – Programmatically Assign Product’s Categories and Sort Order/Position

Related Topic