How to display custom database table in admin grid using Ui component M2.4.5 PHP8.1

Question

I have created a custom module with custom database table with 3 columns. I have setup an admin page with menu etc. and can load the data from the database using the ResourceModel Collection and display on the admin page. This was just to prove my pages load with blocks and the database table and models work properly.

I am now trying to display the custom database data in an admin grid view using ui component. I will add actions etc later.

The page loads with the grid column headers from the xml file so the page mapping is working, but I cannot get the data to display and it just shows "We couldn't find any records."

Grid displays no data

I have followed tons of examples and the official Magento guide on creating a grid using ResourceModel\Grid\Collections class and custom Ui\DataProvider\SalesJobs\DataProvider class but these have not worked for me as I think they are out of date "how-to" guides. I am not mapping to these class now and get the same result.

Can someone let me know where I have gone wrong and "why" my code is not working so I can get an understanding of what's happening. Here's snippets of my code.

My/Module/etc/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd"> 
 <virtualType name="My\Module\Model\ResourceModel\SalesJobs\Grid\Collection"
 type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
 <arguments>
 <argument name="mainTable" xsi:type="string">my_module_sales_jobs</argument>
 <argument name="resourceModel" xsi:type="string">My\Module\Model\ResourceModel\SalesJobs</argument>
 </arguments>
 </virtualType>
 <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
 <arguments>
 <argument name="collections" xsi:type="array">
 <item name="my_module_salesjobs_listing_data_source"
 xsi:type="string">My\Module\Model\ResourceModel\SalesJobs\Grid\Collection</item>
 </argument>
 </arguments>
 </type>
</config>

My\Module\view\adminhtml\ui_component\my_module_salesjobs_listing.xml

<?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">
 <argument name="data" xsi:type="array">
 <item name="js_config" xsi:type="array">
 <item name="provider" xsi:type="string">my_module_salesjobs_listing.my_module_salesjobs_listing_data_source</item>
 <item name="deps" xsi:type="string">my_module_salesjobs_listing.my_module_salesjobs_listing_data_source</item>
 </item>
 <item name="spinner" xsi:type="string">my_module_salesjobs_columns</item>
 </argument>
 <dataSource name="my_module_salesjobs_listing_data_source"
 component="Magento_Ui/js/grid/provider">
 <settings>
 <storageConfig>
 <param name="indexField" xsi:type="string">id</param>
 </storageConfig>
 <updateUrl path="mui/index/render"/>
 </settings>
 <aclResource>Magento_Cms::page</aclResource>
 <dataProvider name="my_module_salesjobs_listing_data_source"
 class="Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider">
 <settings>
 <requestFieldName>id</requestFieldName>
 <primaryFieldName>id</primaryFieldName>
 </settings>
 </dataProvider>
 </dataSource>
 <columns name="my_module_salesjobs_columns">
 <selectionsColumn name="ids">
 <argument name="data" xsi:type="array">
 <item name="config" xsi:type="array">
 <item name="resizeEnabled" xsi:type="boolean">false</item>
 <item name="resizeDefaultWidth" xsi:type="string">55</item>
 <item name="indexField" xsi:type="string">id</item>
 </item>
 </argument>
 </selectionsColumn>
 <column name="id">
 <argument name="data" xsi:type="array">
 <item name="config" xsi:type="array">
 <item name="filter" xsi:type="string">textRange</item>
 <item name="sorting" xsi:type="string">desc</item>
 <item name="label" xsi:type="string" translate="true">ID</item>
 </item>
 </argument>
 </column>
 <column name="order_no" sortOrder="13">
 <settings>
 <filter>text</filter>
 <dataType>text</dataType>
 <label translate="true">Order No</label>
 </settings>
 </column>
 <column name="status" sortOrder="14">
 <settings>
 <filter>text</filter>
 <dataType>text</dataType>
 <label translate="true">Status</label>
 </settings>
 </column>
 </columns>
</listing>

Answer 1

So I just figured this one out myself. The scripts above work and display data as they should.

My issue was that my etc/db_schema.xml index and constraint referenceId fields were slightly different to those auto generated in the etc/db_schema_whitelist.json. So while I could access the db tables using the column names, the admin grid must use the referenceId fields.

I am leaving this here for others who may come across the same issue.

Lots of out of date tutorials out there so be aware and make sure you have up-to-date information. Magento 2.4 Documentation on DB Schema info from here.