Magento – Magento get unique values from custom database table

custom-collectiondatabasemodulesql

I created a custom module with a database table with the help of this article.

I have a program which syncs stock from my physical store to online store. Every time I sync the stock, I add the sku,name, sync data and time, also the qty to the custom database table. There can be multiple entries for the same sku in the table as the same product can be synced multiple times.

Initially I was taking a report of the unique entries from the database table using this, where I was only getting the skus.

$collections = Mage::getResourceModel('module/namespace_collection')->distinct(true)->addFieldToSelect('sku')->load();
foreach( $collections as $collection){
    //add to csv
}

Now I want to get the sku and qty of the unique entries with the latest sync data and time for that particular sku. To get this I tried to use 

$collections = Mage::getResourceModel('module/namespace_collection')->distinct(true)->addFieldToSelect('sku')->addFieldToSelect('qty')->addFieldToSelect('date')->load();

but I am getting all the entries in the custom table. Can some one help me out with this?

EDIT

I found an SQL select to do this

SELECT t.sku,t.date,t.store_name
FROM my_database_table t
INNER JOIN(
    SELECT sku, max(date) as MaxDate
    FROM my_database_table
    GROUP BY sku
) tm on t.sku = tm.sku and t.date = tm.MaxDate;

How do I convert this into a getCollection statement?

Best Answer

In this case,you need to create custom mysql code which will create mysql like this

Call your collection:

$collections = Mage::getResourceModel('module/namespace_collection');

Call the db resource singleton model

 $resource = Mage::getSingleton('core/resource');

Carry connection write connection

$writeConnection = $resource->getConnection('core_write');

Make a custom collection by calling write connection and make a custom subquery:

$subSelect =$writeConnection->select()
            ->from(
                array(' table2' => $collections->getTable('module/namespace')),
                array( 'sku', 'MaxDate'=>new Zend_Db_Expr("max(date)"))
            )
        ->group('table2.sku')

Join table

$collections->getSelect()->join(
                array(' tm' => new Zend_Db_Expr(sprintf('(%s)',$subSelect->assemble()))),
                'main_table.sku = tm.sku and main_table.date = tm.MaxDate,
                array()
            );

Select your selective columns

$collections->addFieldToSelect('sku')->addFieldToSelect('date');

Code is not tested and put basic of concept

Related Topic