Mrthdrb

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 NamespaceModulenameModelResourceModulename;

use MagentoFrameworkModelResourceModelDbCollectionAbstractCollection;

class Collection extends AbstractCollection

 /**
 * Define model & resource model
 */
 const YOUR_TABLE = 'tablename';

 public function __construct(
 MagentoFrameworkDataCollectionEntityFactoryInterface $entityFactory,
 PsrLogLoggerInterface $logger,
 MagentoFrameworkDataCollectionDbFetchStrategyInterface $fetchStrategy,
 MagentoFrameworkEventManagerInterface $eventManager,
 MagentoStoreModelStoreManagerInterface $storeManager,
 MagentoFrameworkDBAdapterAdapterInterface $connection = null,
 MagentoFrameworkModelResourceModelDbAbstractDb $resource = null
 ) 
 $this->_init(
 'NamespaceModulenameModelModulename',
 'NamespaceModulenameModelResourceModulename'
 );
 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']
 );
 

?>

here is a sample for join to product collection

protected $productCollectionFactory;


public function __construct(
 .....
 MagentoCatalogModelResourceModelProductCollectionFactory $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;