0

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)

<?php
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
    ) {
        $this->_init(
            'Namespace\Modulename\Model\Modulename',
            'Namespace\Modulename\Model\Resource\Modulename'
        );
        parent::__construct(
            $entityFactory, $logger, $fetchStrategy, $eventManager, $connection,
            $resource
        );
        $this->storeManager = $storeManager;
    }
    protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
                ['secondTable' => $this->getTable('tablename')],
                'main_table.columnname = secondTable.columnname',
                ['columnname1','columnname2','columnname3']
            );
    }
}
?>
  • what is the issue available after use above query? – Rakesh Jesadiya Oct 25 '16 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 '16 at 6:52
0

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();
            $collection->getSelect()->joinLeft(
                ['productsold'=>$collection->getTable('productsold')],
                'e.entity_id = productsold.product_id',
                ['offsite'=>'productsold.off_sold','soldlabel'=>'productsold.label','soldid'=>'productsold.id']);
    return $collection;   
    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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