The Number Of Publications That Have The Number Of Authors Is Greater Than 4

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

group your data then count items in the group. If the group has more than 4 show the group. This could be slow though on query times with a lot of data. Also, on your relation leave out the limit part.

You could also add a counter field to your publication table that saves a count of the number of authors. I.e. "author_count" …every time a new author is added it would add one ( +1) or if one is taken away it would subtract one (-1). Then query publications with author_count >4

just a though

I think this is the best solution:

// model Publication

    public function relations()

    {

        return array(

            'authorship'=>array(self::HAS_MANY,__NAMESPACE__.'\Authorship','publication_id'),

        );

    }


// controller

        $criteria=new \CDbCriteria;

        $criteria->select='`t`.`id`';

        $criteria->with=array(

            'authorship'=>(array('select'=>false,'joinType'=>'INNER JOIN')),

        );

        $criteria->together=true;

        $criteria->group='`t`.`id`';

        $criteria->having='COUNT(`authorship`.`user_id`) > 4';

        $count=publication_models_asuriddb\Publication::model()->count($criteria);

Yep((

This can be used but it is unreliable.