Custom report of simple-to-configurable product links showing error: Column not found: 1054 Unknown column 'childsku' in 'where clause',

Question

Full error text: Column not found: 1054 Unknown column 'childsku' in 'where clause', query was: SELECT COUNT(*) FROM owd_catalog_product_super_link AS main_table

LEFT JOIN owd_catalog_product_entity AS child_table ON main_table.product_id = child_table.entity_id

LEFT JOIN owd_catalog_product_entity AS parent_table ON main_table.parent_id = parent_table.entity_id

WHERE (childsku LIKE '%bl%')

Built report first by creating model for catalog_product_super_link

function _prepareCollection() contains...

$collection = $this->_superlinkFactory->create()->getCollection();

$collection->getSelect()->joinLeft(array('child_table' => $collection->getTable('catalog_product_entity')), 'main_table.product_id = child_table.entity_id', array('sku as childsku') );

$collection->getSelect()->joinLeft(array('parent_table' => $collection->getTable('catalog_product_entity')), 'main_table.parent_id = parent_table.entity_id', array('sku as parentsku') );

function _prepareColumns() contains...

$this->addColumn( 'childsku', [ 'header' => __('Child SKU'), 'index' => 'childsku', ] );

When filtering on Child SKU column, error occurs.

Answer 1

Figured it out. Realized it was going back to find childsku which is not in actually in the table, and that it needs to read sku from child_table.

Added method to set the correct filter index to the column AFTER the addColumn method, as follows...

$this->addColumn(
    'childsku',
    [
        'header' => __('Child SKU'),
        'index' => 'childsku',
    ]
);
$this->getColumn("childsku")->setFilterIndex("child_table.sku");