Yii Framework Forum: Criteria Generating Wrong Sql If Group Is Given - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Criteria Generating Wrong Sql If Group Is Given Rate Topic: -----

#1 User is offline   invokeargs 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 14-January 13

Posted 14 January 2013 - 01:20 AM

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

0

#2 User is offline   ragua 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 135
  • Joined: 23-January 12
  • Location:Paris - France

Posted 14 January 2013 - 03:52 AM

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

#3 User is offline   invokeargs 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 14-January 13

Posted 14 January 2013 - 09:50 PM

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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users