Hello, I’m having a problem using findBySql() and I don’t know why… When I run the query on MySql it runs perfectly. On my application throw and exception of Unknown Property…
I have 2 tables, one is campaigns and the other saves the users that joined the campaign:
CREATE TABLE IF NOT EXISTS `Pontos`.`dot_campaigns` (
`dot_cam_id` INT NOT NULL AUTO_INCREMENT,
`dot_cam_type_id` SMALLINT NULL,
`dot_cam_cri_id` SMALLINT NULL,
`dot_prod_id` INT NULL,
`dot_cam_com_id` INT NULL,
`dot_cam_crdots` INT NULL,
`dot_cam_totaldots` INT NULL,
`dot_cam_status` INT NULL,
`dot_cam_datestart` DATETIME NULL,
`dot_cam_dateend` DATETIME NULL,
PRIMARY KEY (`dot_cam_id`),
CONSTRAINT `dot_campaign_type`
FOREIGN KEY (`dot_cam_type_id`)
REFERENCES `Pontos`.`dot_campaigns_types` (`dot_cam_type_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `dot_campaign_criterio`
FOREIGN KEY (`dot_cam_cri_id`)
REFERENCES `Pontos`.`dot_campaigns_criterios` (`dot_cam_cri_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `dot_product_campaign`
FOREIGN KEY (`dot_prod_id`)
REFERENCES `Pontos`.`dot_products` (`dot_prod_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `dot_campaign_company`
FOREIGN KEY (`dot_cam_com_id`)
REFERENCES `Pontos`.`dot_companies` (`dot_com_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Pontos`.`dot_user_campaign`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Pontos`.`dot_user_campaign` (
`dot_user_id` INT NOT NULL,
`dot_campaign_id` INT NOT NULL,
`dot_accept` INT NULL,
CONSTRAINT `dot_user_campaign_user`
FOREIGN KEY (`dot_user_id`)
REFERENCES `Pontos`.`dot_users` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `dot_user_campaign_campaign`
FOREIGN KEY (`dot_campaign_id`)
REFERENCES `Pontos`.`dot_campaigns` (`dot_cam_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;
And my search action is:
public function searchBestCampaigns(){
$worker = Worker::findOne(Yii::$app->user->id);
$query = Campaign::findBySql("SELECT
`dot_campaigns`.`dot_cam_id`,
`dot_campaigns`.`dot_cam_cri_id`,
`dot_campaigns`.`dot_prod_id`,
`dot_campaigns`.`dot_cam_com_id`,
`dot_campaigns`.`dot_cam_crdots`,
`dot_campaigns`.`dot_cam_totaldots`,
`dot_campaigns`.`dot_cam_status`,
`dot_campaigns`.`dot_cam_dateend`,
COUNT(`dot_user_campaign`.`dot_accept`) as `TotalUsers`
FROM
`dot_campaigns`
INNER JOIN `dot_user_campaign` ON `dot_campaigns`.`dot_cam_id`= `dot_user_campaign`.`dot_campaign_id`
WHERE
`dot_campaigns`.`dot_cam_com_id` = ".$worker->dot_company_id." and `dot_campaigns`.`dot_cam_status`=0
ORDER BY
`TotalUsers` DESC
GROUP BY
`dot_campaigns`.`dot_cam_id`
");
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
return $dataProvider;
}
The application throws the exception at the field TotalUsers wich is the count() field