/**
* @param integer $id
* @return Query
*/
public static function getRecipeCaloriesQuery($id)
{
/**
* SELECT
* SUM(`dr`.`weight` * COALESCE((
* 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`
* ), 0))
* FROM `diary_recipes` `dr`
* WHERE `dr`.`diary_id` = `d`.`id`
*/
$productCaloriesSql = (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;
return (new Query())->select("SUM(`dr`.`weight`*COALESCE(({$productCaloriesSql}),0)) AS `recipeCalories`")->from(Diary::diary2recipesTableName() . ' `dr`')->where("`dr`.`diary_id` = {$id}");
}
/**
* @return \yii\db\ActiveQuery
*/
public function getRecipes()
{
return $this->hasMany(Recipe::className(), ['id' => 'recipe_id'])->viaTable(Recipe::recipe2productsTableName(), ['product_id' => 'id']);
}
/**
* @param array $params
* @return ArrayDataProvider
*/
public function search($params = [])
{
$query = IngredientEntity::find()->select(['(\'' . IngredientEntity::TYPE_WEIGHT . '\') AS type', 'p.id', 'p.name', 'rp.weight', '`rp`.`weight`*`p`.`calories` AS `calories`', '`rp`.`weight`*`p`.`protein` AS `protein`', '`rp`.`weight`*`p`.`fat` AS `fat`', '`rp`.`weight`*`p`.`carbohydrate` AS `carbohydrate`'])->from(['rp' => Recipe::recipe2productsTableName()])->leftJoin(['p' => Product::tableName()], 'p.id = rp.product_id')->where(['rp.recipe_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;
}
/**
* @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();
}
