Magento 2 create grid for two existing database table

Question

I want to create a grid using uiComponent. I required to get data from two existing table catalog_product_entity and cataloginventory_stock with join query.

Answer 1

You can make one table's collection means example:

make catalog_product_entity's collection and add

  protected function _initSelect()
{
    parent::_initSelect();
      $this->getSelect()->joinRight(
    ['secondTable' => $this->getTable('cataloginventory_stock')],
    'secondTable.entity_id =main_table.customer_id',
  return $this;

}

using _initSelect function you can join the table

Now how grid will connect this collection is important

Above Collection.php extends by Grid/collection.php

and your gird/collection.php's code should be like below:

    namespace vendor\Module\Model\ResourceModel\table\Grid;

  use vendor\Module\Model\ResourceModel\table\Collection as MainCollection;
 use Magento\Framework\Api\Search\SearchResultInterface;
  use Magento\Framework\Search\AggregationInterface;

    class Collection extends MainCollection implements SearchResultInterface {
public function __construct(
    \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory,
    \Psr\Log\LoggerInterface $logger,
    \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
    \Magento\Framework\Event\ManagerInterface $eventManager,
    \Magento\Store\Model\StoreManagerInterface $storeManager,
    $mainTable,
    $eventPrefix,
    $eventObject,
    $resourceModel,
    $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
    $connection = null,
    \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null

    ) {
    parent::__construct(
        $entityFactory,
        $logger,
        $fetchStrategy,
        $eventManager,
        $storeManager,
        $connection,
        $resource
        );
    $this->_eventPrefix = $eventPrefix;
    $this->_eventObject = $eventObject;
    $this->_init($model, $resourceModel);
    $this->setMainTable($mainTable);
}
  /**
 * @return AggregationInterface
 */
  public function getAggregations()
  {
    return $this->aggregations;
  }

/**
 * @param AggregationInterface $aggregations
 *
 * @return $this
 */
public function setAggregations($aggregations)
{
    $this->aggregations = $aggregations;
}


/**
 * Get search criteria.
 *
 * @return \Magento\Framework\Api\SearchCriteriaInterface|null
 */
public function getSearchCriteria()
{
    return null;
}

/**
 * Set search criteria.
 *
 * @param \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria
 *
 * @return $this
 * @SuppressWarnings(PHPMD.UnusedFormalParameter)
 */
public function setSearchCriteria(
    \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria = null
    ) {
    return $this;
}

/**
 * Get total count.
 *
 * @return int
 */
public function getTotalCount()
{
    return $this->getSize();
}

/**
 * Set total count.
 *
 * @param int $totalCount
 *
 * @return $this
 * @SuppressWarnings(PHPMD.UnusedFormalParameter)
 */
public function setTotalCount($totalCount)
{
    return $this;
}

/**
 * Set items list.
 *
 * @param \Magento\Framework\Api\ExtensibleDataInterface[] $items
 *
 * @return $this
 * @SuppressWarnings(PHPMD.UnusedFormalParameter)
 */
public function setItems(array $items = null)
{
    return $this;
}

// public function getItems(){

//     return $this;
// }

}

?>