How to join a table with a Magento 2 repository

Question

I am trying to extend inventory stock items by joining my own table with added information. When the product page is loaded it gets the stock item via StockItemRepository::getList($criteria). So I need to somehow join on my own table here.

My confusion is that with the new Magento 2 repositories rather than M1-style collections, there seems to be no option to join tables on.

To give a specific example, here is the contents of that getList method:

public function getList(\Magento\CatalogInventory\Api\StockItemCriteriaInterface $criteria)
{
    $queryBuilder = $this->queryBuilderFactory->create();
    $queryBuilder->setCriteria($criteria);
    $queryBuilder->setResource($this->resource);
    $query = $queryBuilder->create();
    $collection = $this->stockItemCollectionFactory->create(['query' => $query]);
    return $collection;
}

The queryBuilder is a simple class that just creates the select statement from the criteria, and the collection is de-coupled from the database query entirely. Although I can add fields and filters to the criteria much like a M1 collection, I cannot see where I would add a join here.

Am I barking up the wrong tree entirely, or have I missed where I would add a join?

Answer 1

I've solved this by adding the join to the criteria mapper. It appears that when building a query for a collection, the criteria object contains the high-level search parameters for how you'd like to filter your data, and the mapper converts this into a Zend-Db style select.

Here is what I have added to a class extending and overriding the \Magento\CatalogInventory\Model\ResourceModel\Stock\Item\StockItemCriteriaMapper class in order to add to the protected init() method to add an additional qty to stock items:

protected function init()
{
    parent::init();
    $this->getSelect()->joinleft(
        ['qty_additional_table' => $this->getTable('cataloginventory_qty_additional')],
        'main_table.item_id = qty_additional_table.item_id',
        ['qty_additional']
    );
}

(Note that I am aware that this is only one change of many needed to actually load, modify and save this extra value)

Answer 2

You can join table by this way

//Join table
$collection->getSelect()->join('second_table as second', 'main_table.entity_id = second.entity_id');

//Print Query
$collection->getSelect()->__toString();