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

0

just add code

echo $this->getSelect();
exit;

it will print your query

  • 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 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);
    }
  • But how i can write the Above query in it. Can you please guide more – Sheri May 6 at 10:13
  • @sheri updated answer – Sunil Patel May 6 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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