Hi all who read this post.
There are 3 tables:
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`email` VARCHAR(200) NULL DEFAULT NULL COMMENT 'e-mail',
`password` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Password',
`last_name` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Last Name',
`name` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Name',
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date of registration',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
CREATE TABLE `publication` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Publication title',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
CREATE TABLE `authorship` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_id` INT(11) NOT NULL COMMENT 'Author',
`publication_id` INT(11) NULL DEFAULT NULL COMMENT 'Publication',
PRIMARY KEY (`id`),
INDEX `FK_authorship_user` (`user_id`),
INDEX `FK_authorship_publication` (`publication_id`),
CONSTRAINT `FK_authorship_publication` FOREIGN KEY (`publication_id`) REFERENCES `publication` (`id`) ON DELETE CASCADE,
CONSTRAINT `FK_authorship_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
Relationship M:M between the publications and authors (extra fields I’ve omitted). How can I use Yii ActiveRecords to calculate the number of publications that have more than 4 authors? If it is impossible to use Yii ActiveRecords to calculate will you other any alternative variants to do this.
I try this:
// model Publication
public function relations()
{
return array(
'authorCount'=>array(self::STAT,__NAMESPACE__.'\Authorship','publication_id','having'=>'COUNT(DISTINCT `t`.`id`) > 4'),
);
}
// controller
$criteria=new \CDbCriteria;
$criteria->with=array(
'authorCount',
);
$criteria->together=true;
$count=publication_models_asuriddb\Publication::model()->count($criteria);
This variant is false because static requests are calculated separately and only at the first time when I use the data. I tried to add a suitable condition to $criteria (after removing the line ‘having’=>‘COUNT(DISTINCT t
.id
) > 4’ from relationship), it’s also wrong (for the same reason).