Criteria Generating Wrong Sql If Group Is Given

Yii 1.1 General Discussion
1

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...
2

Hi,

this is purely SQL problem.

You didn’t need to group your datas If id is the key of your document table, because you use




select D.*, ...

from ... as D

Just remove the group part.

3

same result when i remove the ‘group’, its working if i also remove the ‘having’ but i need that for my filter in gridview.

the D.* is needed because fields in this table is used in criteria compare