How To Write This Select In Query In Query Builder

Yii 1.1 General Discussion
1

Hi,

I’m trying to execute this query in querybuilder.




SELECT id, title, area_id From item WHERE item.area_id IN (SELECT item.area_id FROM item WHERE item.id = 3)

I’ve read the docs for the Where() In operator but i’m still having some trouble getting it to execute. What would be the correct way?

Thanks

Jonny

2

instead of this

SELECT id, title, area_id From item WHERE item.area_id IN (SELECT item.area_id FROM item WHERE item.id = 3)

can you try this?

SELECT

id, title, area_id

FROM

item a


INNER JOIN item b


   ON a.area_id =b.id

WHERE

b.id = 3
3

Thanks for the reply, yours worked in my SQL client. But I seem to be having a real problem with backticks (I think).

CDbCommand::() failed: 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

'.title, i.area_id, i.slug…

SELECT i.id, AS i.title, i.area_id, a.slug

FROM item i

JOIN item b ON i.area_id = b.id

WHERE i.id=:id.

Is how it was being executed.

I had the same outcome when trying to use this method http://www.yiiframework.com/forum/index.php/topic/26020-yii-query-builder-how-to-construct-in-condition-mix-with-other-and-condition/

4

I removed the $cmd->select as an array and just quoted it like:

$cmd->select = ‘i.id, i.title, i.slug’;

Hope that help anyone else.