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

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);
    }