How can i use a complex sql in the AR

Yii 1.1 General Discussion
1

I have 3 models: Employee,Group and GroupStruct.

and have 4 tables: Employee,Group,GroupEmployee and GroupStruct.

the table schema is:




--

-- table `Employee`

--


CREATE TABLE IF NOT EXISTS `employee` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(180) NOT NULL,

  `email` varchar(180) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB;


--

-- table `Group`

--


CREATE TABLE IF NOT EXISTS `group` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(180) NOT NULL,

  PRIMARY KEY (`id`),

) ENGINE=InnoDB;


--

-- table `GroupEmployee`

--


CREATE TABLE IF NOT EXISTS `groupemployee` (

  `group_id` int(11) NOT NULL,

  `employee_id` int(11) NOT NULL,

  PRIMARY KEY (`group_id`,`employee_id`),

  KEY `FK_employee` (`employee_id`)

) ENGINE=InnoDB;


--

-- table `GroupStruct`

--


CREATE TABLE IF NOT EXISTS `groupstruct` (

  `employee_id` int(11) NOT NULL,

  `group_id` int(11) NOT NULL,

  `parent_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`employee_id`,`group_id`),

  KEY `FK_groupstruct_employee_parent` (`parent_id`),

  KEY `FK_groupstruct_group` (`group_id`)

) ENGINE=InnoDB;

I want get employees not in the groupstruct.

I write a SQL,the reqults is right,but I don’t know how to implement in the model with AR,please help.




SELECT a. *

FROM employee AS a

LEFT JOIN groupemployee AS b ON b.employee_id = a.id

AND b.group_id =1

WHERE a.id NOT

IN (

  SELECT employee_id

  FROM groupstruct

  WHERE group_id =1

)
2

you can use http://www.yiiframework.com/doc/api/1.0.11/CDbCommandBuilder (createInCondition) see the class

or see http://www.yiiframework.com/doc/guide/database.dao

or put the condition like this




$criteria->condition= 't.id NOT IN (SELECT employee_id FROM groupstruct WHERE group_id =1)';

//the alias is t not a
3

thank you very much!