Skip to content

How to apply join with custom table with customer_entity table in Magento2.4

I want to fetching data with customer_entity table and created custom table but gives some error in admin grid

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cus_account.customer_id' in 'on clause', query was: SELECT `main_table`.*, `customer`.* FROM `cus_account` AS `main_table` INNER JOIN `customer_entity` AS `customer` ON cus_account.customer_id = customer.entity_id LIMIT 20

This is my di.xml file

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <!-- here we remove virtualType and defile collection as follow-->
    <type name="AmarnathCusaccountModelResourceModelCusassignGridCollection">
        <arguments>
            <argument name="mainTable" xsi:type="string">cus_account</argument>
          <!--<argument name="eventPrefix" xsi:type="string">cus_account_grid_collection</argument>
            <argument name="eventObject" xsi:type="string">cus_account_collection</argument> -->
            <argument name="resourceModel" xsi:type="string">AmarnathCusaccountModelResourceModelCusassign</argument>
        </arguments>
    </type>
    <type name="MagentoFrameworkViewElementUiComponentDataProviderCollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <!--data provider name which used in grid ui component file -->
                <item name="cusaccount_listing_data_source" xsi:type="string">AmarnathCusaccountModelResourceModelCusassignGridCollection</item>
            </argument>
        </arguments>
    </type>
</config>

This is my model collection file

<?php

namespace AmarnathCusaccountModelResourceModelCusassign;

use MagentoFrameworkModelResourceModelDbCollectionAbstractCollection;

class Collection extends AbstractCollection
{
    protected function _construct()
    {
        $this->_init('AmarnathCusaccountModelCusassign', 'AmarnathCusaccountModelResourceModelCusassign');
    }
    protected function _initSelect()
    {
        parent::_initSelect();

        $joinTable = $this->getTable('customer_entity');
        $this->getSelect()->join($joinTable.' as customer','cus_account.customer_id = customer.entity_id', array('*'));
    }
}