i have this code from the search method, this generates an error “CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 126 Incorrect key file for table ‘/tmp/#sql_155f_1.MYI’; try to repair it” when i check the logs generated sql is incorrect (see below)
$criteria->alias = 'D';
$criteria->select = array(
'D.*',
'IF((PD.ProductId IN (SELECT ProductId FROM client_products))=1, "1", "0") as linked',
);
$criteria->join = 'LEFT JOIN product_documents AS PD ON D.Id = PD.DocumentId';
$criteria->group = 'PD.DocumentId';
$criteria->having = 'linked like ' . Yii::app()->db->quoteValue('%' . $this->linked);
failed: SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_155f_1.MYI'; try to repair it.
The SQL statement executed was:
SELECT COUNT(*) FROM (SELECT D.*, IF((PD.ProductId IN (SELECT ProductId FROM client_products))=1, "1", "0") as linked FROM `documents` D LEFT JOIN product_documents AS PD ON D.Id = PD.DocumentId GROUP BY PD.DocumentId) sq.
desired sql output is
SELECT D.*, IF((PD.ProductId IN (SELECT ProductId FROM client_products))=1, "1", "0") as linked
FROM `documents` as D
left join product_documents as PD on PD.DocumentId=D.Id
group by D.Id...