Database table columns are displayed in custom grid in adminhtml. One of the field has name 'precision' and this causes SQL error when I sort the grid by this field (as this is mysql reserved word and must be used like this:

ORDER BY `precision` ASC

The question is: how to escape it or use an alias to avoid SQL error?

This field is defined in xml file like this:

    <column name="precision">
        <argument name="data" xsi:type="array">
            <item name="js_config" xsi:type="array">
                <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/column</item>
            <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="validate-digits-range" xsi:type="string">0-8</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="dataType" xsi:type="string">text</item>
                <item name="align" xsi:type="string">left</item>
                <item name="label" xsi:type="string" translate="true">Precision (0 - 8)</item>
                <item name="sortOrder" xsi:type="number">60</item>

The solution is quite simple. In Collection.php:

protected function _construct()
    $this->_init(MyClass::class, MyClassResourceModel::class);
    $this->_map['fields']['precision'] = 'main_table.`precision`';
    // any of these strings will do the same
    //$this->addFilterToMap('precision', 'main_table.`precision`');
    //$this->addFilterToMap('precision', 'main_table.precision');
    //$this->addFilterToMap('precision', '`precision`');

So if your field name is something like 'table' or 'count' you can do this trick to avoid SQL errors

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.