After join, use the result join again in Magento2

Question

For example, I create a table which has product sku, I use the table LEFT JOIN `catalog_product_entity` table to get the entity_id, then want to use the entity_id result to LEFT JOIN `catalog_product_entity_media_gallery_value_to_entity` table to get the product gallery value_id.

I write the function like below:

protected function _initSelect()
{
    parent::_initSelect();

    $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('catalog_product_entity')],
        'main_table.sku = secondTable.sku',
        ['entity_id']
    )->joinLeft(
        ['thirdTable' => $this->getTable('catalog_product_entity_media_gallery_value_to_entity')],
        'main_table.entity_id = thirdTable.entity_id',
        ['value_id']
    );
}

But, it doesn't work, it shows warning like below:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.entity_id' in 'on clause', query was: SELECT COUNT(*) FROM `my_table` AS `main_table` LEFT JOIN `catalog_product_entity` AS `secondTable` ON main_table.sku = secondTable.sku LEFT JOIN `catalog_product_entity_media_gallery_value_to_entity` AS `thirdTable` ON main_table.entity_id = thirdTable.entity_id

So, how to achieve after join, use the result join again in Magento2?

Answer 1

Your table does't have the entity_id row, right ?. Try to use secondTable.entity_id instead of main_table.entity_id.