How to compare value inside array in magento 2 join query?

Question

$this->getSelect()->join(
["address" => "sales_order_address"],
'main_table.entity_id = address.parent_id AND address.address_type = "shipping"',
array('region')
)->joinLeft(
["so" => "sales_order_tax"],
'main_table.entity_id = so.order_id',
array('sgst' => strcmp('address.region','Maharashtra') == 0 ? new \Zend_Db_Expr('SUM(amount)/2') : new \Zend_Db_Expr(0),'cgst' => strcmp('address.region','Maharashtra') == 0 ? new \Zend_Db_Expr('SUM(amount)/2') : new \Zend_Db_Expr(0),'igst' => strcmp('address.region','Maharashtra') != 0 ? new \Zend_Db_Expr('SUM(amount)') : new \Zend_Db_Expr(0),'amount' => new \Zend_Db_Expr('SUM(amount)'))
)
->group('so.order_id');

I need to compare the region in the shipping address and based on that i need to set the sgst, cgst and igst value in the sales order grid. what is the correct syntax to compare.

Answer 1

Finally, I found the solution using case inside Zend_Db_Expr

$productionState = "Maharashtra";

$this->getSelect()->join(
["address" => "sales_order_address"],
'main_table.entity_id = address.parent_id AND address.address_type = "shipping"',
array('region')
)->joinLeft(
["so" => "sales_order_tax"],
'main_table.entity_id = so.order_id',
array('sgst' => new \Zend_Db_Expr('CASE WHEN STRCMP(address.region, "'.$productionState.'") = 0 THEN SUM(amount)/2 ELSE 0 END'),
'cgst' => new \Zend_Db_Expr('CASE WHEN STRCMP(address.region, "'.$productionState.'") = 0 THEN SUM(amount)/2 ELSE 0 END'),
'igst' =>new \Zend_Db_Expr('CASE WHEN STRCMP(address.region, "'.$productionState.'") != 0 THEN SUM(amount) ELSE 0 END'),
'amount' => new \Zend_Db_Expr('SUM(amount)'))
)
->group('so.order_id');