5

I have 3 different tables each table has some data, now i want to join all 3 tables and get the data.

How to do that??

When i searched in google i found this function but its not working for me

protected function _renderData() {
    $joinTable = $this->getTable('table_1');
    $this->getSelect()->join($joinTable.' as tab','main_table.item_code = tab.item_id', array('*'));
}
8

You can join the tables as added below to your collection in Magento 2. The difference between M1 and M2 is getting table name.

$this->getSelect()->join(
            ['parent_product' => $this->getTable('catalog_product_entity')],
            'parent_product.entity_id = i.entity_id',
            []
        )->join(
            ['bo' => $this->getTable('catalog_product_bundle_option')],
            "bo.parent_id = parent_product.$linkField",
            ['option_id']
        )->join(
            ['bs' => $this->getTable('catalog_product_bundle_selection')],
            'bs.option_id = bo.option_id',
            ['selection_id']
        )->joinLeft(
            ['bsp' => $this->getTable('catalog_product_bundle_selection_price')],
            'bs.selection_id = bsp.selection_id AND bsp.website_id = i.website_id',
            ['']
        )->where(
            'i.price_type=?',
            $priceType
        );

Hope this helps.

4
  • hi krishna where should i need to write this .....for 3 table i have 3 models resource class and collection, so can u tell me where to write this?? Aug 18 '16 at 7:33
  • Render the collection with main table ( Assume you have main table collection collection). then join the tables to the collection. $this->_collection->join(['w' => 'table_1'], 'main_table.website_id=w.website_id'); Aug 18 '16 at 7:56
  • Follow the basic how to use models and collections mage-world.com/blog/… Aug 18 '16 at 7:58
  • I was not able to get ` ->where( 'i.price_type=?', $priceType ); ` to work. Though I did find ` ->addFieldToFilter('i.price_type', ['eq' => $priceType]) ` did the trick Aug 2 '17 at 19:18
4

Below code might help you in joining the tables. Considering that you are joining them in some function, suppose of any block file. Now you have to first inject your firsttable model to get collection and resourceConnection to get table name.

    public function __construct(
    \Magento\Framework\View\Element\Template\Context $context,
    \W3solver\Helloworld\Model\FirstFactory $FirstModelFactory,
    \Magento\Framework\App\ResourceConnection $Resource
    ) {

    }

Now in any of your function join your table as per below code:-

public function getJoinData(){
        $collection = $this->_helloModelFactory->create()->getCollection();
        $second_table_name = $this->_resource->getTableName('second_table_name'); 

        $collection->getSelect()->joinLeft(array('second' => $second_table_name),
                                               'main_table.id = second.customer_id');
        echo $collection->getSelect()->__toString(); 
        exit();

    }

Similary you can join any number of table. To review more check this link(Magento 2 Join Table)

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.