Hi there,
I’m having a problem with relational active record. Should be simple, I’ve done this many times before, but have taken some time off programming and it feels like I’ve forgotten everything!!!
If I have the following tables.
CREATE TABLE `person_group` (
`person_id` INT(7) NOT NULL,
`group_id` INT(11) NOT NULL,
PRIMARY KEY (`person_id`,`group_id`),
INDEX `FK_pg_person` (`person_id` ASC),
INDEX `FK_pg_group` (`group_id` ASC),
CONSTRAINT `pg_1`
FOREIGN KEY (`person_id`)
REFERENCES `person` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `pg_2`
FOREIGN KEY (`group_id`)
REFERENCES `group` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
CREATE TABLE `group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
In my Person model I have defined a relation as follows…
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'groups' => array(self::MANY_MANY, 'Group', 'person_group(person_id, group_id)'),
);
}
How would I go about creating a CActiveDataProvider where it selects all of the persons in a particular group.
I have tried this…
public function actionIndex()
{
$group=1;
$model = new Person;
$criteria=new CDbCriteria;
$criteria->with='groups';
$criteria->condition = 'groups.id>=:group';
$criteria->params = array(':group'=>(int)$group);
$dataProvider=new CActiveDataProvider($model, array(
'criteria'=>$criteria,
'pagination'=>array(
'pageSize'=>42,
),
));
$this->render('index',array(
'dataProvider'=>$dataProvider,
));
}
but I get this error. CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘groups.id’ in ‘where clause’. The SQL statement executed was: SELECT t.id AS t0_c0, t.name AS t0_c1, t.age AS t0_c2 FROM person t WHERE (groups.id>=:group) LIMIT 42
If I run it without the criteria condition then the SQL is
Querying SQL: SELECT t.id AS t0_c0, groups.id AS t1_c0,
groups.name AS t1_c1 FROM person t LEFT OUTER JOIN person_group
groups_groups ON (t.id=groups_groups.person_id) LEFT OUTER JOIN
group groups ON (groups.id=groups_groups.group_id) WHERE
(t.id IN (1, 2, 3, 4))
For some reason, it seems to drop the JOIN when I add the criteria->condition