Magento – Modify core tables vs custom tables and joins

databaseextensionsgrid

I'm aware that there are multiple different ways of adding a custom column to the order grid, from overloading the block to custom layout handle with event observer for loading data. My question is more about where this custom data should be stored. Historically speaking we have achieved this by adding custom columns to the sales_flat_quote, sales_flat_order and sales_flat_order_grid tables as appropriate and copying the data over using the fieldsets in the configuration.

This process works and has suited our needs to this point, however modification of core tables in this manner feels a little dirty. This approach could cause issues with later upgrades to Magento and multiple modules working in this manner could cause compatibility issues with third party modules. This seems especially concerning in the case of us deciding to distribute an extension.

The only other approach that springs to mind is if our Cool_Feature extension was to create it's own cool_feature_order_extra table, probably with a foreign key reference to the sales_flat_order table, that we can then join whenever we need the information.

Is there a alternative (neater?) approach which I've neglected?
Is there a 'Magento best practice' way of doing it?
Has anybody ran into any issues with either approach?

Best Answer

Without knowing exactly what your extension will do it's hard to say which direction I should recommend you go. But, generally, iI believe that it is best practice to extend and add columns to sales_flat_* tables, as evidenced by Magento's own extensions that they have implemented.

For instance, in Enterprise Edition instead of creating join tables for sales data on Enterprise_GiftWrapping, for instance, they extend sales_flat_order and sales_flat_order_item to include relevant gw_* fields to contain sales information as needed.

In my opinion there is little to no danger in making your edits to these tables on a 1.x install of Magento - unless, of course, you're wanting to support legacy Magento versions in which case sales data was EAV until ~1.4CE.

A potential argument against using join tables is that any join in the admin that keys against a flat table used for order placement (such as sales_flat_order) has the potential of affecting a lock upon that table which may cause unintended problems for high-volume stores.

I had an admin sort by customer_email in the order grid view (a custom extension) - the email address is unindexed and the sort was on > 300k rows. This caused 3 orders to fail with a lock wait timeout, but payments had been made and authorized in the gateway. What's worse is that the customers were then unable to complete their order subsequently due to duplicate transaction id numbers, a limitation of the gateway.

So, proceed with caution... but in my opinion you're clear to edit the core tables.

Related Solutions

Magento – Adding data to custom module grid using FK relationship

I believe you can display the foreign table values by defining a class in the admin_html/ui_component/YOUR_GRID_XML_FILE as follows (GIVEN EXAMPLE IS FOR ADMIN USER with Access as the name of the controller / model )

 <column name="user_id" class="YOUR_COMPANY_NAMESPACE\MODULE_NAME\Ui\Component\Listing\Column\AccessActions" >
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="editor" xsi:type="array">
                    <item name="editorType" xsi:type="string">text</item>
                    <item name="validation" xsi:type="array">
                        <item name="required-entry" xsi:type="boolean">true</item>
                    </item>
                </item>
                <item name="filter" xsi:type="string">text</item>
                <item name="label" xsi:type="string" translate="true">User ID</item>
            </item>
        </argument>
    </column>

Once you have defined your user id field to a class we should create the file at the same path and form the data as follows:

  <?php

namespace YOUR_COMPANY\MODULE_NAME\Ui\Component\Listing\Column;

use Magento\Framework\UrlInterface;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Ui\Component\Listing\Columns\Column;

/**
 * Class TestActions
 */
class AccessActions extends Column
{

    /**
     * @var UserFactory
     */

    protected $userFactory;

    /**
     * Constructor
     *
     * @param ContextInterface $context
     * @param UiComponentFactory $uiComponentFactory
     * @param UserFactory $userFactory
     * @param array $components
     * @param array $data
     */
    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        \Magento\User\Model\UserFactory $userFactory,
        array $components = [],
        array $data = []
    ) {
        $this->userFactory = $userFactory;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    /**
     * Prepare Data Source
     *
     * @param array $dataSource
     * @return array
     */
    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                if (isset($item['access_id'])) {


                       if($this->getData('name') == "user_id"){
                         $user = $this->userFactory->create()->load($item['user_id']);
                         $item[$this->getData('name')]=[$user->getUsername()];

                       }
                }
            }
        }

        return $dataSource;
    }
}

The above will post the user name instead of user id.

Best Answer

Related Solutions

Magento – Adding data to custom module grid using FK relationship

Related Topic