How to limit the results from a Left Join in Magento?

Question

I have a collection that I need to join with another collection... the problem is the table contains 125k rows and so it times out.

        //Fields
        $this->addFieldToSelect('id');
        $this->addFieldToSelect('subject');
        $this->addFieldToSelect('email_address');
        $this->addFieldToSelect('mail_id');
        $this->addFieldToSelect('date_sent');
        $this->addFieldToSelect('customer_id');

        //Get latest status
        $this->getSelect()->joinLeft(
            array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
            'main_table.id = me1.email_id',
            'event_type as current_status'
        );

       $this->getSelect()->joinLeft(
            array('me2' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
            '(main_table.id = me2.email_id AND (me1.timestamp < me2.timestamp OR me1.timestamp = me2.timestamp AND me1.id < me2.id))',
            false
        );

So I need to limit just what comes back in the joinLeft()... how is that possible?

Answer 1

At the end you can limit the records you want

$this->getSelect()->limit(20);

Answer 2

This work for me:

$this->getSelect()->joinLeft(
        array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
        'me1.event_type = (SELECT event_type FROM me1 WHERE main_table.id = me1.email_id LIMIT 1)',
        'event_type as current_status'
    );

but it's got very bad perfomance impact.

Hope some one find a better solution.

Answer 3

I did this recently with a query

Solution was mysql function with group by

https://github.com/DominicWatts/CustomerInfo/blob/master/Model/ResourceModel/Grid/Collection.php#L20-L29

$this->getSelect()->joinLeft(
            ['customer_log' => $this->getTable('customer_log')],
            'customer_log.customer_id = main_table.entity_id',
            ['last_login_at']
        )->joinLeft(
            ['sales_order_grid' => $this->getTable('sales_order_grid')],
            'sales_order_grid.customer_id = customer_log.customer_id',
            ['max(sales_order_grid.created_at) as order_created_at']
        )
        ->group('main_table.entity_id');

Answer 4

I check and understand Mage::getResourceModel('someprovider_mail/event')->getMainTable() is grow day by day i think. limit is not a good solution for this.

I have provided two good solutions for this.

Solution 1:: In your query, you can narrow down some more based on Date or any other fields.

Solution 2:: Try to create another table what are the fields you need, Write an insert query based on your needs and set up in crontab run every day or twice a day and store in the created table. This is what all reporting companies are storing and retrieve data fast.

Any other best solution also appreciated.

Answer 5

Whenever I'm dealing with large collections I do something like this

https://www.atwix.com/magento/working-with-large-collections/

public function updateIndex()
{
    $productsCollection = Mage::getModel('catalog/product')
        ->getCollection()
        ->addAttributeToSelect(array('name', 'image', 'url_key', 'price', 'visibility'));

    $productsCollection->setPageSize(100);
    $pages = $productsCollection->getLastPageNumber();
    $currentPage = 1;

    do {
        $productsCollection->setCurPage($currentPage);
        $productsCollection->load();
        foreach ($productsCollection as $_product) {
            // $_product->getId()
            // etc
                
                
        }
        $currentPage++;
        //clear collection and free memory
        $productsCollection->clear();
    } while ($currentPage <= $pages);
}

So should be able to use the query as it currently is. Might work for you might not. Depends on what you are doing with the data. If you are dumping to a CSV for example should be able to get this running in a shell script and leave it running. If however, you are creating an admin grid for example this won't work.

Things like this can be tricky. Good luck.