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('*'));
}

3 Answers 3

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, 2016 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, 2016 at 7:56
  • Follow the basic how to use models and collections mage-world.com/blog/… Aug 18, 2016 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, 2017 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)

#####################################################

Information from (Magento 2 Join Table). Site currently dead

STEP 1

Setup a collection class as we have did in our last blog post

W3solver\Helloworld\Model\ResourceModel\Hello\Collection.php
<?php 
namespace W3solver\Helloworld\Model\ResourceModel\Hello;

class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{
    /**
     * @var string
     */
    protected $_idFieldName = 'id';

    /**
     * Define resource model
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('W3solver\Helloworld\Model\Hello', 'W3solver\Helloworld\Model\ResourceModel\Hello');
    }

}

STEP 2

Now we will place function that will join two of our tables and will give us the query to join. We will be injecting our hello model in block class constructor to get the collection and will join it with another table in getJoinData function.

<?php

namespace W3solver\Helloworld\Block;

class Hello extends \Magento\Framework\View\Element\Template {

    protected $_helloModelFactory;
    protected $_resource;
    
    public function __construct(
    \Magento\Framework\View\Element\Template\Context $context,
    \W3solver\Helloworld\Model\HelloFactory $helloModelFactory,
    \Magento\Framework\App\ResourceConnection $Resource
    ) {
        $this->_helloModelFactory = $helloModelFactory;
        $this->_resource = $Resource;
        
        parent::__construct($context);
    }

    protected function _prepareLayout()
    {
        $text = $this->getJoinData();
        $this->setText($test);
    }
    
    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();
        
    }
}

Above we have injected hello model of first table and resourceConnection class to get table name. Then we get the collection of first table which is by default alias as “main_table” in magento. This will give you the query which you can verify as per your requirement.

0
    function getColumns($table,$prefix)
{
    $resource = $GLOBALS['obj']->get('Magento\Framework\App\ResourceConnection');
    $connection = $resource->getConnection();
    $sql = "SHOW COLUMNS FROM `$table`;";
    echo $sql;
    $result = $connection->fetchAll($sql);
    $columns = array();
    for ($i = 0; $i < count($result); $i++) {
        array_push($columns,$result[$i]['Field']." as ".$prefix.$result[$i]['Field']);
    }
    return $columns;
}

If you want the resulting data with prefix for other join table then use above function to get column array with prefix

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.