Skip to content

Magento 2 RightJoin Query have extra Join condition

I’m using a query to provide data for my UI component.

$catalogrule = $this->getTable('catalogrule');
        $this->getSelect()
        ->reset(Zend_Db_Select::COLUMNS)
        ->columns(['s_id', 'created_at', 'created_by', 'sync_status', 'error'])
        ->joinRight(
            ['p' =>$catalogrule],
            'main_table.magento_id = p.rule_id',
            ['entity_id' => 'p.rule_id','name' => 'p.name']
        )
        ->group('p.rule_id');
        parent::_renderFiltersBefore();

but when I print this query using print_r($this->getSelect()->__toString());
SELECT `main_table`.`s_id`, `main_table`.`created_at`, `main_table`.`created_by`, `main_table`.`sync_status`, `main_table`.`error`, `p`.`rule_id` AS `entity_id`, `p`.`name` FROM `MainTableName` AS `main_table` RIGHT JOIN `catalogrule` AS `p` ON main_table.magento_id = p.rule_id AND (p.created_in <= '1711954744' AND p.updated_in > '1711954744') GROUP BY `p`.`rule_id`

The Query is adding AND (p.created_in <= '1711954744' AND p.updated_in > '1711954744') condition inside ON and because of this I’m not able to get all the data for UI component.