/** * Creates data provider instance with search query applied * * @param array $params * * @return ArrayDataProvider */ public function search($params = []) { $caloriesQuery = RecipeRepository::getCaloriesQuery('dr.recipe_id')->createCommand()->sql; $proteinsQuery = RecipeRepository::getProteinsQuery('dr.recipe_id')->createCommand()->sql; $fatsQuery = RecipeRepository::getFatsQuery('dr.recipe_id')->createCommand()->sql; $carbohydratesQuery = RecipeRepository::getCarbohydratesQuery('dr.recipe_id')->createCommand()->sql; $query = IngredientEntity::find()->select(['(\'' . IngredientEntity::TYPE_WEIGHT . '\') AS type', 'r.id', 'r.name', 'dr.weight', "({$caloriesQuery}) * `dr`.`weight` AS `calories`", "({$proteinsQuery}) * `dr`.`weight` AS `protein`", "({$fatsQuery}) * `dr`.`weight` AS `fat`", "({$carbohydratesQuery}) * `dr`.`weight` AS `carbohydrate`"])->from(['dr' => Diary::diary2recipesTableName()])->leftJoin(['r' => Recipe::tableName()], 'r.id = dr.recipe_id')->where(['dr.diary_id' => $this->id])->orderBy('calories DESC'); $dataProvider = new ArrayDataProvider(['allModels' => $query->all(), 'sort' => ['attributes' => ['name', 'weight', 'calories', 'protein', 'fat', 'carbohydrate'], 'defaultOrder' => ['name' => SORT_ASC]], 'pagination' => false]); return $dataProvider; }
/** * @return array */ public function getRecipeIngredients() { if ($this->_recipesIngredients !== null) { return $this->_recipesIngredients; } $this->_recipesIngredients = []; $caloriesSql = (new Query())->select('SUM(`prod`.`calories`*`rp`.`weight`)/SUM(`rp`.`weight`)')->from(Recipe::recipe2productsTableName() . ' `rp`')->leftJoin(Product::tableName() . ' `prod`', '`prod`.`id` = `rp`.`product_id`')->where('`rp`.`recipe_id` = `dr`.`recipe_id`')->createCommand()->sql; /** * SELECT * `r`.`id` AS `id`, * `r`.`name` AS `name`, * `dr`.`weight`*( * SELECT * SUM(`prod`.`calories`*`rp`.`weight`)/SUM(`rp`.`weight`) * FROM `recipe_products` `rp` * LEFT JOIN `product` `prod` ON `prod`.`id` = `rp`.`product_id` * WHERE `rp`.`recipe_id` = `dr`.`recipe_id` * ) AS `calories`, * `dr`.`weight` AS `weight` * FROM `diary_recipes` `dr` * LEFT JOIN `recipe` `r` ON `r`.`id` = `dr`.`recipe_id` * WHERE `dr`.`diary_id` = 1 */ $ingredients = (new Query())->select(['`r`.`id` AS `id`', '`r`.`name` AS `name`', "`dr`.`weight`*({$caloriesSql}) AS `calories`", '`dr`.`weight` AS `weight`'])->from(self::diary2recipesTableName() . ' `dr`')->leftJoin(Recipe::tableName() . ' `r`', '`r`.`id` = `dr`.`recipe_id`')->where(['`dr`.`diary_id`' => $this->id])->all(); if (!empty($ingredients)) { $this->_recipesIngredients = $ingredients; } return $this->_recipesIngredients; }
/** * @param integer|string $id * @return Query */ public static function getPortionsCountQuery($id) { //portionsCount return (new Query())->select('COUNT(*) AS `portionsCount`')->from(Portion::tableName() . ' `p`')->leftJoin(Recipe::tableName() . ' `r`', '`r`.`id` = `p`.`recipe_id`')->where("`r`.`category_id` = {$id}"); }
/** * @param array|string|integer $params * @return Portion */ public static function searchOne($params) { $where = is_array($params) ? $params : ['p.id' => $params]; return Portion::find()->select(['p.*', 'p.weight', 'categoryName' => 'c.name', 'recipeName' => 'r.name', 'calories' => 'SUM(`rp`.`weight`*`prod`.`calories`)/SUM(`rp`.`weight`)*`p`.`weight`', 'proteins' => 'SUM(`rp`.`weight`*`prod`.`protein`)/SUM(`rp`.`weight`)*`p`.`weight`', 'fats' => 'SUM(`rp`.`weight`*`prod`.`fat`)/SUM(`rp`.`weight`)*`p`.`weight`', 'carbohydrates' => 'SUM(`rp`.`weight`*`prod`.`carbohydrate`)/SUM(`rp`.`weight`)*`p`.`weight`'])->from(['p' => Portion::tableName()])->innerJoin(['r' => Recipe::tableName()], '`r`.`id` = `p`.`recipe_id`')->innerJoin(['rp' => Recipe::recipe2productsTableName()], '`rp`.`recipe_id` = `r`.`id`')->leftJoin(['c' => RecipeCategory::tableName()], '`c`.`id` = `r`.`category_id`')->leftJoin(['prod' => Product::tableName()], '`prod`.`id` = `rp`.`product_id`')->where($where)->one(); }