1

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

2 Answers 2

0

just add code

echo $this->getSelect();
exit;

it will print your query

1
  • 1
    Thanks for answering. Actually i want to apply the above query for grid collection / instead of printing. Let me correct my question.
    – Sheri
    May 6, 2019 at 8:15
0
 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);
    }
2
  • But how i can write the Above query in it. Can you please guide more
    – Sheri
    May 6, 2019 at 10:13
  • @sheri updated answer May 6, 2019 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.