How to join two custom table in Magento 2.3

How to join two custom table in Magento 2.3

October 1, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to join two custom table in Magento 2.3

Joining Two Custom Tables in Magento 2.3

In Magento 2.3, joining two custom tables involves creating a collection that combines data from both tables based on the relationship between them. Let’s explore how to join the pscustom_catalog_product_entity and pscustom_catalog_product_entity_media tables using the resource model and block.

Understanding Your Current Setup

You have already created resource models for both tables:

  • pscustom_catalog_product_entity: Handles the main product data.
  • pscustom_catalog_product_entity_media: Stores media files associated with products.

Your current implementation allows you to retrieve a collection of products using the getAllProducts() method in your Product block. However, this collection only contains data from the pscustom_catalog_product_entity table.

The Challenge: Joining Two Collections

To join these two collections, we need to perform a SQL join operation on the tables based on the foreign key relationship between them. In this case, the product ID (entity_id) in the pscustom_catalog_product_entity_media table should match the primary key of the pscustom_catalog_product_entity table.

The Solution: Creating a Composite Collection

We can solve this by creating a composite collection that merges data from both tables. Here’s how to achieve this:

  1. Create a new model for the composite collection:
// app/code/MyModule/Productsinfo/Model/CompositeProduct.php
namespace MyModule\Productsinfo\Model;

use Magento\Framework\Model\AbstractModel;
use MyModule\Productsinfo\Model\ResourceModel\CompositeProduct as ResourceModel;

class CompositeProduct extends AbstractModel
{
    protected function _construct()
    {
        $this->init(ResourceModel::class);
    }
}
  1. Update the resource model to handle the join:
// app/code/MyModule/Productsinfo/Model/ResourceModel/CompositeProduct.php
namespace MyModule\Productsinfo\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Magento\Framework\DB\Select;

class CompositeProduct extends AbstractDb
{
    protected function _construct()
    {
        $this->init('pscustom_catalog_product_entity', 'entity_id');
    }

    protected function _afterLoad()
    {
        parent::_afterLoad();

        $select = $this->_getReadAdapter()->select();
        $select->from(['cpe' => $this->getMainTable()])
            ->joinLeft(
                ['cpe_media' => $this->getTable('pscustom_catalog_product_entity_media')],
                'cpe.entity_id = cpe_media.product_id',
                []
            );

        if ($this->hasSelected()) {
            $select->where('cpe.entity_id IN (?)', $this->getSelected());
        }

        $this->_joinData($select);
    }
}
  1. Update the composite product collection:
// app/code/MyModule/Productsinfo/Model/ResourceModel/CompositeProduct/Collection.php
namespace MyModule\Productsinfo\Model\ResourceModel\CompositeProduct;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use MyModule\Productsinfo\Model\CompositeProduct as Model;
use MyModule\Productsinfo\Model\ResourceModel\CompositeProduct as ResourceModel;

class Collection extends AbstractCollection
{
    protected function _construct()
    {
        $this->init(Model::class, ResourceModel::class);
    }
}
  1. Update your Product block to use the composite collection:
// app/code/MyModule/Productsinfo/Block/Product.php
namespace MyModule\Productsinfo\Block;

use Magento\Framework\View\Element\Template;
use MyModule\Productsinfo\Model\ResourceModel\CompositeProduct\Collection;

class Product extends Template
{
    // ... existing code ...

    /**
     * @var Collection
     */
    private $collection;

    public function __construct(
        Template\Context $context,
        Collection $collection,
        array $data = []
    )
    {
        parent::__construct($context, $data);
        $this->collection = $collection;
    }

    public function getAllProducts() {
        return $this->collection;
    }
}

What We’ve Achieved

By following these steps, you have successfully joined the pscustom_catalog_product_entity and pscustom_catalog_product_entity_media tables in Magento 2.3.

The CompositeProduct model extends the AbstractModel, allowing it to inherit all necessary functionality for interacting with the database and handling data retrieval.

In the resource model, the _afterLoad() method is overridden to execute a SQL join between the main product table (pscustom_catalog_product_entity) and the media table. This join ensures that each product is associated with its relevant media files when the collection is loaded.

Finally, the Product block is updated to use this new composite collection, providing access to the joined data in your views or templates.

Conclusion

Joining custom tables in Magento 2.3 requires a solid understanding of the resource model and how collections are constructed. By creating a composite model and updating the resource model to perform an SQL join, you can efficiently retrieve and display data from multiple tables within a single collection.

Remember that this example demonstrates a simple join operation between two tables. In real-world scenarios, you might need to handle more complex relationships or apply additional filters based on your business requirements.

For further guidance on Magento development and best practices, feel free to explore our articles at Laravel Company.

Magento 2 Catalog Database Extensions

Our network