How to write Nested Query in Grid Collection _initSelect method

Question

I want to Apply this Query using $this->getSelect() method

SELECT * 
FROM 
 (SELECT * FROM customer_grid_flat ORDER BY enroller_id, entity_id) customer_grid_flat, (SELECT @pv := '$id') initialisation 
WHERE 
FIND_IN_SET(enroller_id, @pv) > 0 AND @pv := CONCAT(@pv, ',', entity_id);

Func _initSelect() under Collection

protected function _initSelect()
{
 parent::_initSelect();
 $id = $this->helperdata->getCurrentId();
$this->getSelect()->reset(\Zend_Db_Select::FROM)
->from(
['maintable' => new \Zend_Db_Expr('(SELECT enroller_id, entity_id FROM customer_grid_flat) customer_grid_flat, (SELECT @pv := "$id") initialisation')],
['customer_grid_flat.entity_id', new \Zend_Db_Expr('COUNT(*)')]
)
->where('FIND_IN_SET(enroller_id, @pv) > 0 AND @pv := CONCAT(@pv, ",", entity_id)');
return $this;

}

Result

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

'AS maintable WHERE (FIND_IN_SET(enroller_id, @pv)> 0 AND @pv := CONCAT(@pv, "' at line 1,

query was: SELECT COUNT(*) FROM (SELECT enroller_id, entity_id FROM customer_grid_flat) customer_grid_flat, (SELECT @pv := "2") initialisation AS maintable WHERE (FIND_IN_SET(enroller_id, @pv)> 0 AND @pv := CONCAT(@pv, ",", entity_id))

Answer 1

just add code

echo $this->getSelect();
exit;

it will print your query

• Sheri: Thanks for answering. Actually i want to apply the above query for grid collection / instead of printing. Let me correct my question.

Answer 2

 public function loadWithFilter(
 $printQuery = false,
 $logQuery = false
 ) {
 // you need to implement this functon
$this->getSelect()->reset(\Zend_Db_Select::FROM)
->from(
['maintable' => new \Zend_Db_Expr('(SELECT enroller_id, entity_id FROM customer_grid_flat) customer_grid_flat, (SELECT @pv := "$id") initialisation')],
['customer_grid_flat.entity_id', new \Zend_Db_Expr('COUNT(*)')]
)
->where('FIND_IN_SET(enroller_id, @pv) > 0 AND @pv := CONCAT(@pv, ",", entity_id)');
 return parent::loadWithFilter($printQuery, $logQuery);
 }

• Sheri: But how i can write the Above query in it. Can you please guide more
• Sunil Patel: @sheri updated answer