Yii2 项目中关联表太多数据查询优化 [ 2.0 版本 ]

由于业务原因导致数据库设计嵌套关系太多，有的场景需要查询多张表才可以，实现，目前2种解放方案

方案1：使用 yii2 自带的 hasOne 和 hasMany

public function getRecipe()
{
    return $this->hasOne(Recipe::className(), ['id' => 'recipe_id']);
}

public function getMealMaterials()
{
    return $this->hasMany(MealMaterial::className(), ['meal_detail_id' => 'id']);
}

public function getMaterials()
{
    return $this->hasMany(Material::className(), ['id' => 'material_id'])
        ->via('mealMaterials');
}

方案2：直接使用原生sql查询

$userOneDayMeal = MealDetail::find()
    ->select([
        'meal_detail.id as meal_detail_id',
        'meal_detail.stage',
        'meal_detail.recipe_id',
        'meal_detail.plate',
        'meal_material.weight',
        'recipe.name',
        'image.path as image',
        'material.name as material_name',
        'material.energy'
    ])
    ->leftJoin('recipe', "recipe.id = meal_detail.recipe_id")
    ->leftJoin('image', "image.id = recipe.image_id")
    ->leftJoin('meal_material', "meal_material.meal_detail_id = meal_detail.id")
    ->leftJoin('material', "material.id = meal_material.material_id")
    ->where(
        ['and',
            ['meal_detail.meal_id'=>$mealId],
            ['meal_detail.day'=>$day],
        ]
    )
    ->orderBy(['stage' => SORT_ASC, 'plate' => SORT_ASC,])
    ->asArray()->all();

备注：那种实现方式好，目前没有考虑缓存啥的