I have created 10 columns. If I am using 9 columns then the grid is loading, but once I enable 10th column it's giving an error:
error:"UI component could not be rendered because of system exception",errorcode: "42"
Here when i am adding 10th column
average_orders_peryear
then
error:"UI component could not be rendered because of system exception",
errorcode: "42"
Below is my uiComponent listing file.
<?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.