0

I have created 10 columns and but if i am using 9 columns then grid is loading but once i enable 10th column it's giving an

error:"UI component could not be rendered because of system exception",errorcode: "42"

enter image description here

Below is my uiComponent listing file Here when i am adding 10th column average_orders_peryear then error:"UI component could not be rendered because of system exception", errorcode: "42"

<?xml version="1.0" encoding="UTF-8"?>

<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <!-- Integration -->
    <argument name="data" xsi:type="array">
        <item name="js_config" xsi:type="array">
            <!-- we define a provider -->
            <item name="provider" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing_data_source</item>
            <!-- same string as above -->
            <item name="deps" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing_data_source</item>
        </item>

        <!-- define column type -->
        <item name="spinner" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_columns</item>

    </argument>
    <!-- Data source -->
    <dataSource name="customerlifetimevalue_report_customerlifetimevalue_listing_data_source">
        <argument name="dataProvider" xsi:type="configurableObject">
            <argument name="class" xsi:type="string">CustomerlifetimeValueReportGridDataProvider</argument> <!-- Data provider class -->
            <argument name="name" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing_data_source</argument> <!-- provider defined above -->
            <argument name="primaryFieldName" xsi:type="string">entity_id</argument> <!-- Primary key -->
            <argument name="requestFieldName" xsi:type="string">id</argument> <!-- URL name parameter -->

            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/provider</item>
                    <item name="update_url" xsi:type="url" path="mui/index/render"/>
                    <item name="storageConfig" xsi:type="array">
                        <!-- Primary key column name -->
                        <item name="indexField" xsi:type="string">entity_id</item>
                    </item>
                </item>
            </argument>
        </argument>
    </dataSource>

    <!-- Container Listing Top -->
    <container name="listing_top">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="template" xsi:type="string">ui/grid/toolbar</item>
            </item>
        </argument>
        
        <bookmark name="bookmarks">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/controls/bookmarks/bookmarks</item>
                    <item name="displayArea" xsi:type="string">dataGridActions</item>
                    <item name="storageConfig" xsi:type="array">
                        <item name="saveUrl" xsi:type="url" path="mui/bookmark/save"/>
                        <item name="deleteUrl" xsi:type="url" path="mui/bookmark/delete"/>
                        <item name="namespace" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing</item>
                    </item>
                </item>
            </argument>
        </bookmark>
        
        <filters name="listing_filters">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="storageConfig" xsi:type="array">
                        <item name="provider" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing.listing_top.bookmarks</item>
                        <item name="namespace" xsi:type="string">current.filters</item>
                    </item>
                    <item name="childDefaults" xsi:type="array">
                        <item name="provider" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing.listing_top.listing_filters</item>
                        <item name="imports" xsi:type="array">
                            <item name="visible" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing.listing_top.bookmarks:current.columns.${ $.index }.visible</item>
                        </item>
                    </item>
                </item>
                <item name="observers" xsi:type="array">
                    <item name="column" xsi:type="string">column</item>
                </item>
            </argument>
        </filters>
        <!-- <columnsControls name="columns_controls">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="displayArea" xsi:type="boolean">false</item>
                </item>
            </argument>
        </columnsControls> -->
        
        <container name="columns_controls">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="columnsData" xsi:type="array">
                        <item name="provider" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_columns</item>
                    </item>
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/controls/columns</item>
                    <item name="displayArea" xsi:type="string">dataGridActions</item>
                </item>
            </argument>
        </container>
       <!--  <exportButton name="export_button" /> -->
          <!-- Paging -->
        <paging name="listing_paging">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="storageConfig" xsi:type="array">
                        <item name="provider" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing.listing_top.bookmarks</item>
                        <item name="namespace" xsi:type="string">current.paging</item>
                    </item>
                    <item name="selectProvider" xsi:type="string">customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_listing.customerlifetimevalue_report_customerlifetimevalue_columns.ids</item>
                    <item name="displayArea" xsi:type="string">bottom</item>
                </item>
            </argument>
        </paging>
        
        <exportButton class="Magento\Ui\Component\ExportButton">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="options" xsi:type="array">
                        <item name="csv" xsi:type="array">
                            <item name="value" xsi:type="string">csv</item>
                            <item name="label" xsi:type="string" translate="true">CSV</item>
                            <item name="url" xsi:type="string">mui/export/gridToCsv</item>
                        </item>
                        <item name="xml" xsi:type="array">
                            <item name="value" xsi:type="string">xml</item>
                            <item name="label" xsi:type="string" translate="true">Excel XML</item>
                            <item name="url" xsi:type="string">mui/export/gridToXml</item>                          
                        </item> 
                    </item>
                </item>
            </argument>
        </exportButton>

    </container>

    <!-- Columns -->
    <columns name="customerlifetimevalue_report_customerlifetimevalue_columns" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo">
       <selectionsColumn name="ids" sortOrder="10">
            <settings>
                <indexField>entity_id</indexField>
            </settings>
        </selectionsColumn>

        
         <column name="customer_id" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">false</item>
                    <item name="label" xsi:type="string" translate="true">Customer ID</item>
                </item>
            </argument>
        </column>
        
        
        <column name="customer_id" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo" sortOrder="20">
            <settings>
                <filter>false</filter>
                <sorting>desc</sorting>
                <label translate="true">Customer ID</label>
            </settings>
        </column>
        
        <column name="city" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo" sortOrder="30">
             <settings>
                <filter>false</filter>
                <label translate="true">City</label>
            </settings>
        </column>
        
        
        
        <column name="postcode" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo" sortOrder="40">
             <settings>
                    <filter>false</filter>
                    <label translate="true">Postcode</label>
             </settings>
        </column>
        
        
        
        <column name="country" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo" sortOrder="50">
            <settings>
                        <filter>false</filter>
                        <label translate="true">Country</label>
            </settings>
        </column>
        

        <column name="number_of_orders" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo"  sortOrder="60">
            <settings>
                        <filter>false</filter>
                        <label translate="true">Total orders</label>
            </settings>
        </column>
        
        <column name="first_order_purchase_date" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo"  sortOrder="70">
            <settings>
                        <filter>false</filter>
                        <label translate="true">Date of first purchase</label>
            </settings>
        </column>
        

        <column name="last_order_purchase_date" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo"  sortOrder="80">
            <settings>
                        <filter>false</filter>
                        <label translate="true">Last order date</label>
            </settings>
        </column>
        

        <column name="order_total" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo"  sortOrder="90">
            <settings>
                        <filter>false</filter>
                        <label translate="true">Total orders value</label>
            </settings>
        </column>

        <column name="average_order_total" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo"  sortOrder="100">
            <settings>
                        <filter>false</filter>
                        <label translate="true">Average order value</label>
            </settings>
        </column>
        
        <column name="average_orders_peryear" class="Vendor\Module\Ui\Component\Listing\Column\Metainfo"  sortOrder="110">
            <settings>
                        <filter>false</filter>
                        <sorting>desc</sorting>
                        <label translate="true">Average orders per year</label>
            </settings>
        </column>

    </columns>
</listing>

Vendor\Module\Model\ResourceModel\CustomerlifetimeValue\Collection.php

<?php

namespace Vendor\Module\Model\ResourceModel\CustomerlifetimeValue;

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

class Collection extends AbstractCollection
{
    protected function _construct()
    {
        $this->_init('Vendor\Module\Model\CustomerlifetimeValue', 'Vendor\Module\Model\ResourceModel\CustomerlifetimeValue');
    }
    
    protected function _initSelect()
    {
        parent::_initSelect();
        
         $this->addFilterToMap('entity_id', 'main_table.entity_id');

         $sales_order = $this->getTable('sales_order');
        
         $this->getSelect()->join(
            ['sales_order' => $sales_order],
            'main_table.entity_id = sales_order.customer_id',
            [
                'sales_order.customer_id','main_table.*'
            ]
        )->group('sales_order.customer_id')->order('main_table.entity_id','DESC'); 
        
        return $this;
    } 
}

Error below:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'average_orders_peryear' in 'order clause', query was: SELECT `main_table`.*, `sales_order`.`customer_id`, `main_table`.* FROM `customer_grid_flat` AS `main_table` INNER JOIN `sales_order` ON main_table.entity_id = sales_order.customer_id GROUP BY `sales_order`.`customer_id` ORDER BY `main_table`.`entity_id` ASC, average_orders_peryear DESC LIMIT 20

In above error 10th columns average_orders_peryear also printing query.

By reviewing the above code if any idea please answer. Thanks in Advance!!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.