What is the best practice way in Magento 2 for creating Many To Many relationships?

Question

I've looked around the core and seen a few examples of many to many relationships between models, but I can't see a definitive answer on this.

As an example, say we create a new model and we want to have a many to many relationship with existing products table.

So we have our new Model - Stockist, and we create 2 tables as such, one to store the Stockist name, the other to store the many to many relationship with products.

Truncated version of setup classes:

$table = $setup->getConnection()
        ->newTable($installer->getTable('stockist'))
        ->addColumn('stockist_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
            'Stockist Id')
        ->addColumn('name',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            null,
            ['nullable' => false],
            'Stockist Name');

---

 $table = $installer->getConnection()
            ->newTable($installer->getTable('stockist_product'))
            ->addColumn(
                'entity_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                ['identity' => true, 'nullable' => false, 'primary' => true],
                'Entity ID'
            )
            ->addColumn(
                'stockist_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                ['unsigned' => true, 'nullable' => false, 'primary' => true, 'default' => '0'],
                'Stockist ID'
            )
            ->addColumn(
                'product_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                ['unsigned' => true, 'nullable' => false, 'primary' => true, 'default' => '0'],
                'Product ID'
            )
            ->addIndex(
                $installer->getIdxName('stockist_product', ['product_id']),
                ['product_id']
            )
            ->addIndex(
                $installer->getIdxName(
                    'stockist_product,
                    ['stockist_id', 'product_id'],
                    \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
                ),
                ['stockist_id', 'product_id'],
                ['type' => \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE]
            )
            ->addForeignKey(
                $installer->getFkName('stockist_product', 'product_id', 'catalog_product_entity', 'entity_id'),
                'product_id',
                $installer->getTable('catalog_product_entity'),
                'entity_id',
                \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
            )
            ->addForeignKey(
                $installer->getFkName('stockist_product', 'stockist_id', 'stockist', 'stockist_id'),
                'stockist_id',
                $installer->getTable('stockist'),
                'stockist_id',
                \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
            )
            ->setComment('Stockist to Product Many to Many');

Then we create a standard Model/ResourceModel/Collection for Stockist as so:

namespace OurModule\Stockist\Model;

use Magento\Framework\Model\AbstractModel;

class Stockist extends AbstractModel
{

    protected function _construct()
    {
        $this->_init('OurModule\Stockist\Model\ResourceModel\Stockist');
    }

}

---

namespace OurModule\Stockist\Model\ResourceModel;

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

class Stockist extends AbstractDb
{

    protected function _construct()
    {
        $this->_init('stockist', 'stockist_id');
    }

}

---

namespace OurModule\Stockist\Model\ResourceModel\Stockist;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
{

    public function _construct()
    {
        $this->_init('OurModule\Stockist\Model\Stockist', 'OurModule\Stockist\Model\ResourceModel\Stockist');
    }

}

This is where we come to how to handle the table with the many to many relationship. So far I've come up with something along the lines of this.

Create a model to represent StockistProduct

namespace OurModule\Stockist\Model;

use Magento\Framework\Model\AbstractModel;

class StockistProduct extends AbstractModel
{

protected function _construct()
{
    $this->_init('OurModule\Stockist\Model\ResourceModel\StockistProduct');
}

/**
 * @param array $productIds
 */
public function getStockists($productIds)
{
    return $this->_getResource()->getStockists($productIds);
}

/**
 * @param array $stockistIds
 */
public function getProducts($stockistIds)
{
    return $this->_getResource()->getProducts($stockistIds);
}
}

Here defining 2 methods that will take in either an array of stockist Ids, returning an array of matching Product Ids and vice-versa.

This uses a Resource Model for the stockist_product table containing the many to many relationship:

/**
 * Class StockistProduct
 */
class StockistProduct extends AbstractDb
{
    /**
     * Model initialization
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('stockist_product', 'entity_id');
    }

    /**
     * Retrieve product stockist Ids
     *
     * @param array $productIds
     * @return array
     */
    public function getStockists(array $productIds)
    {
        $select = $this->getConnection()->select()->from(
            $this->getMainTable(),
            ['product_id', 'stockist_id']
        )->where(
            'product_id IN (?)',
            $productIds
        );
        $rowset = $this->getConnection()->fetchAll($select);

        $result = [];
        foreach ($rowset as $row) {
            $result[$row['product_id']][] = $row['stockist_id'];
        }

        return $result;
    }


    /**
     * Retrieve stockist product Ids
     *
     * @param array $stockistIds
     * @return array
     */
    public function getProducts(array $stockistIds)
    {
        $select = $this->getConnection()->select()->from(
            $this->getMainTable(),
            ['product_id', 'stockist_id']
        )->where(
            'stockist_id IN (?)',
            $stockistIds
        );
        $rowset = $this->getConnection()->fetchAll($select);

        $result = [];
        foreach ($rowset as $row) {
            $result[$row['product_id']][] = $row['stockist_id'];
        }

        return $result;
    }
}

Then using this StockistProduct model when you need to retrieve a set of either model as so, assuming we have a Product Model in $product, and $stockistProduct is an instance of \OurModule\Stockist\Model\StockistProduct

$stockists = $stockistProduct->getStockists([$product->getId()]);

We can then create each model in turn by looping the list of Ids returned as so, where $stockistFactory is an instance of \OurModule\Stockist\Model\StockistFactory

$stockist = $this->stockistFactory->create();
$stockist->load($stockistId);

This all works fine, and is based on some similar code within the Core of Magento 2, but I can't help but wonder if there is a better way?

Answer 1

I implemented a solution similar to this. For each SKU, there was "fitment" information: year, make, model of a car to which the product (car accessory) could be applied. On the face of it, this would be easiest with native Magento attributes. Just use three text fields, one for year, one for make, one for model. This allows all the built-in Magento functionality, like searching and filtering with these attributes, along with easy updating in the future.

The problem, as you describe, is that we need "many" of these relationships. We could make 30 text attributes: year1, make1, model1, year2, make2, model2, ... year10, make10, model10. This would a) likely leave many empty attributes, and b) create an artificial limit on the number of cars which a product supports.

What could work is something like this:

Year: ____
Make: ____
Model: ____

Add new YearMakeModel relationship (+)

And then after clicking the plus (+) you would see:

Year: ____
Make: ____
Model: ____

Year: ____
Make: ____
Model: ____

Add new YearMakeModel relationship (+)

Such a UI could be implemented with javascript inside a backed theme template. Upon submitting the form, you would need to provide this data to Magento as product attributes. I don't think there is currently an attribute type which supports a dynamic length. You would be implementing a custom attribute type. Again, this provides support from built-in Magento functionality: searching on entered attributes, easy update for these attributes in the future.

In the end, our client made the decision to save money by not implementing this "easy editing," and instead we locked away the data in a custom table, just as you describe. I have a custom import script that takes CSV input and outputs to the table. Later, the product page (well, its block) makes queries to this table, pulls out the info about its SKU, and displays to the user as a table. This product page table was the desired behavior from the client, so for us it didn't make sense to dig into doing it "The Magento Way" and implementing a variable-member attribute.