Get Latest Entry For Duplicate Rows In Collection

Question

I currently have a logs table which has the following rows which use the same order_id:

log_id	order_id	user	action
2410	1001318004	test1	Declined
2409	1001318004	test1	Declined

Inside my app/code/Vendor/Module/Model/ResourceModel/OrderTable/Collection.php class file I have the following method to set the collection results:

protected function _initSelect()
    {
        $this->getSelect()->from(['main_table' => $this->getMainTable()]);

        $this->getSelect()
            ->joinLeft(
                ['logs' => $this->getTable('logs')],
                'main_table.increment_id = logs.order_id',
                ['*','customer_name'=>'CONCAT_WS(" ",main_table.customer_firstname,main_table.customer_lastname)']
            );

        return $this->filterOrders();
    }

where main_table references the sales_order table.

but when I view the results in the admin I get the following error:

Item (Magento\Framework\View\Element\UiComponent\DataProvider\Document) with the same ID "1557472" already exists.

The 1557472 refers to the entity ID matching the order for the 1001318004 increment ID.

What I'm looking for, is a way to only get the latest row if there are duplicates in the results.

Cheers guys

Answer 1

Ok, so after a long time of troubleshooting this, I have finally resolved it. So here is the solution for anyone else who encounters a similar problem.

The initial method:

protected function _initSelect()
    {
        $this->getSelect()->from(['main_table' => $this->getMainTable()]);

        $this->getSelect()
            ->joinLeft(
                ['logs' => $this->getTable('logs')],
                'main_table.increment_id = logs.order_id',
                ['*','customer_name'=>'CONCAT_WS(" ",main_table.customer_firstname,main_table.customer_lastname)']
            );

        return $this->filterOrders();
    }

the new method:

protected function _initSelect()
    {
        $this->getSelect()->from(['main_table' => $this->getMainTable()]);

        $connection = $this->getConnection();

        // first we create a query which gets the max value for the row id, and then groups the results on order_id 
        $groupedResults = $connection->select();
        $groupedResults
            ->from(['logs_grouped' => 'logs'])
            ->reset($groupedResults::COLUMNS)
            ->columns(
                [
                    'max_id' => new \Zend_Db_Expr("max(`logs_grouped`.`log_id`)"),
                ]
            )
            ->group('order_id');

        // we create another query which will match the row id values, with the max id values from our grouped set, giving us the latest entries from the duplicates
        $logs = $connection->select();
        $logs
            ->from(['logs' => 'logs'])
            ->reset($logs::COLUMNS)
            ->columns(
                '*'
            )->joinInner(
                ['logs_grouped' => $groupedResults],
                'logs.log_id = logs_grouped.max_id'
            );

        // finally, we left join our new $logs table to the main table (in this case the sales_order table)
        $this->getSelect()
            ->joinLeft(
                ['logs' => $logs],
                'main_table.increment_id = logs.order_id',
                ['*','customer_name'=>'CONCAT_WS(" ",main_table.customer_firstname,main_table.customer_lastname)']
            );

        return $this;
    }

so instead of:

log_id	order_id	user	action
2410	1001318004	test1	Declined
2409	1001318004	test1	Declined

we now get:

log_id	order_id	user	action
2410	1001318004	test1	Declined

giving us the row with the latest id :)