How to get data from 2 database tables and show them in the custom frontend page? I have been looking for the tutorials, so far what i get is creating SQL Left Join in the Collection.php (even though i do not know whether this is working or not)

namespace Namespace\Modulename\Model\Resource\Modulename;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
     * Define model & resource model
    const YOUR_TABLE = 'tablename';

    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,
        \Magento\Framework\DB\Adapter\AdapterInterface $connection = null,
        \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
    ) {
            $entityFactory, $logger, $fetchStrategy, $eventManager, $connection,
        $this->storeManager = $storeManager;
    protected function _initSelect()

                ['secondTable' => $this->getTable('tablename')],
                'main_table.columnname = secondTable.columnname',
  • what is the issue available after use above query? Oct 25, 2016 at 4:55
  • @Rakesh well that query is an answer from magento.stackexchange.com/a/124204/44801, but that was for creating admin grid that only leave Collection and di.xml, whereas what i need is display data to the frontend page. So i haven't tried above code
    – Seventh St
    Oct 25, 2016 at 6:52

1 Answer 1


Here is a sample for join to product collection,

protected $productCollectionFactory;

public function __construct(
    \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
) {
    $this->productCollectionFactory = $productCollectionFactory;

public function getCollection()
    $collection = $this->productCollectionFactory->create();
        'e.entity_id = productsold.product_id',

    return $collection;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.