如何在由自定义分隔符分隔的单个列中一起显示两个组concat列

Question

The follow is the controller function to display the table

$rec = DB::table('recipe_list')
    ->select('recipe_list.recipe_id', 'recipe_list.Recipe_name',
        'recipe_list.Recipe_desc', 'recipe_list.Recipe_duration',
        DB::raw('group_concat(distinct ing_list.Ing_name separator ",") as recipe_ingredients'),
        DB::raw('group_concat(distinct recipe_inglist.quantity  separator ",") as ingredients_amount'),
        'recipe_cusine.Cusine_name', 'recipe_type.Recipe_type_name', 'recipe_list.image',
        DB::raw(' count(distinct likerecipes.id) as likes'))
    ->join('recipe_inglist', 'recipe_list.recipe_id', '=', 'recipe_inglist.Recipe_id')
    ->join('ing_list', 'recipe_inglist.Ing_id', '=', 'ing_list.ing_id')
    ->join('recipe_cusine', 'recipe_list.Recipe_cusine_id', '=', 'recipe_cusine.cusine_id')
    ->join('recipe_type', 'recipe_list.Recipe_type_id', '=', 'recipe_type.Recipe_typeID')
    ->join('likerecipes', 'recipe_list.recipe_id', '=', 'likerecipes.recipe_id')
    ->where('recipe_list.recipe_id', '>=', '1')
    ->groupBy('recipe_list.recipe_id', 'recipe_list.recipe_name', 'recipe_list.recipe_desc', 'recipe_list.recipe_duration', 'recipe_cusine.Cusine_name', 'recipe_type.Recipe_type_name', 'recipe_list.image')->get();

the table output is Recipes Table

But what I want the output to be is like :

|Recipe_id|Name|Description|Cusine|Type |Ingredient        |
|   1     |blah|blahblahbla| bla  | bla | 20 gm of garlic,
                                        | 500 gm of chicken,
                                        | 30 gm of onion,

This is my recipe_inglist table recipe_inglist

Answer 1

I recommend you to go with php code,

Like this way,

You have $rec data, then do below code,

foreach ($rec as $k => &$v) {
    $names  = explode(",", $v->recipe_ingredients);
    $amount = explode(",", $v->ingredients_amount);
    $temp   = [];
    foreach ($names as $key => $value) {
        $temp[] = $amount[$key] . ' of ' . $value;
    }
    if ($temp) {
        $v->ingredient = implode(",", $temp);
    }

}

Where I am creating ingradient field which will have e.g. 20 gm of garlic,500 gm of chicken,30 gm of onion as value.