Magento – Magento 2.2.5: How to join a table with a repository

databasemagento2sql

I am trying to join table 'cataloginventory_stock_status' with order item table using OrderItemRepositoryInterface and searchCriteriaBuilder.

$searchCriteria = $this->searchCriteriaBuilder
                       ->addFilter('product_type', 'simple', 'eq')
                       ->create();
$collection = $this->orderItemRepository->getList($searchCriteria);

$collection->getSelect()->join(array('stock' => 'cataloginventory_stock_status'), 'main_table.product_id = stock.product_id', 'stock.stock_status')
               ->where('stock.stock_status = 1')
               ->columns('SUM(qty_ordered) as total_qty')
               ->group('main_table.product_id')
               ->order(array('total_qty DESC', 'name ASC'));
return $collection;

When i echo $collection->getSelect();

then its show write query, but got wrong data on template file.

Any solution for this please.

Best Answer

Just use collection directly by injecting it in your constructor instead of repository because actual implementations of getList method are not powerful enough to permit additional joins.

public function __construct(\Magento\Sales\Model\ResourceModel\Order\Item\CollectionFactory $collectionFactory)
{
    $this->orderItemCollectionFactory = $collectionFactory;
}

...

$collection = $this->orderItemCollectionFactory->create();
$collection->addFieldToFilter('product_type', 'simple');
$collection->getSelect()->join(array('stock' => 'cataloginventory_stock_status'), 'main_table.product_id = stock.product_id', 'stock.stock_status')
               ->where('stock.stock_status = 1')
               ->columns('SUM(qty_ordered) as total_qty')
               ->group('main_table.product_id')
               ->order(array('total_qty DESC', 'name ASC'));
return $collection;
Related Topic