Formatting nested json with query on laravel

Question

how to do nested json on these table i want a result that would look like this.

{
  "data":[
    {
      "id":2,
      "name":"miles",
      "email":"sb-o8c47x137031@personal.example.com",
      "details":[
        {
          "id":1,
          "user_id":"2",
          "product_id":"product-003",
          "label":"purchase rebate",
          "status":"unpaid",
          "amount":"2.00",
          "process_date":"2019-09-20 10:18:22"
        },
        {
          "id":2,
          "user_id":"2",
          "product_id":"product-003",
          "label":"survey answer rebate",
          "status":"unpaid",
          "amount":"2.00",
          "process_date":"2019-09-20 10:24:06"
        }
      ]
    },
    {
      "id":3,
      "name":"Jayson",
      "email":"sb-rv9me150874@personal.example.com",
      "details":[

      ]
    }
  ]
}

heres my server side script

$merchant_users = DB::table('merchant_users as a')
    ->leftjoin('user_rebate_transactions as b', 'b.user_id','=','a.id') 
    ->select('a.id','a.name',DB::raw('SUM(b.amount) as total'),'a.email')
    ->where('b.status','unpaid')
    ->groupBy('a.id','a.name','a.email')->get();
$some1 = json_decode($merchant_users, true);            
$out = [];
foreach($some1 as $merchant_users){
    $query = DB::table('user_rebate_transactions')->select('*')
        ->where([['user_id','=',$merchant_users['id']],['status','=','unpaid']])->get();
    // echo json_encode($query);
    $out['data'][] = ['id' => $merchant_users['id'],
        'name' => $merchant_users['name'],
        'email' => $merchant_users['email'],
        'total' => $merchant_users['total'],
        'details' => $query];                
}
return json_encode($out, JSON_PRETTY_PRINT);

here's my result

{
  "data":[
    {
      "id":2,
      "name":"miles",
      "email":"sb-o8c47x137031@personal.example.com",
      "total":"4.00",
      "details":[
        {
          "id":1,
          "user_id":"2",
          "product_id":"product-003",
          "label":"purchase rebate",
          "status":"unpaid",
          "amount":"2.00",
          "process_date":"2019-09-20 10:18:22"
        },
        {
          "id":2,
          "user_id":"2",
          "product_id":"product-003",
          "label":"survey answer rebate",
          "status":"unpaid",
          "amount":"2.00",
          "process_date":"2019-09-20 10:24:06"
        }
      ]
    }
  ]
}

my table structure is merchant user table AND user rebate transactions table

Answer 1

$merchant_users = DB::table('merchant_users')
        ->select('*')
        ->get();
        $some1 = json_decode($merchant_users, true);            
        $out = [];
        foreach($some1 as $user){
            //query the transactions rebate details
            $transaction_details = DB::table('merchant_users as a')
            ->leftjoin('user_rebate_transactions as b', 'b.user_id','=','a.id') 
            ->select('b.id','b.user_id','b.product_id','b.label','b.amount','b.status','b.process_date')
            ->where([['b.user_id','=',$user['id']],['status','=','unpaid']])
            ->get();
            //query the transactions rebate details get the total amount of rebate
            $get_total_amount_rebate = DB::table('merchant_users as a')
            ->leftjoin('user_rebate_transactions as b', 'b.user_id','=','a.id') 
            ->select(DB::raw('SUM(b.amount) as total'))
            ->where([['b.user_id','=',$user['id']],['status','=','unpaid']])
            ->get();
            $total_decode = json_decode($get_total_amount_rebate,true);
            if(isset($total_decode[0]['total']))
            {
                $total = $total_decode[0]['total'];
            }
            else{
                $total = 0;
            }                
                $out['data'][] = ['id' => $user['id'],
                'name' => $user['name'],
                'email' => $user['email'],
                'total' => $total,
                'details' => $transaction_details];
        }
        return json_encode($out, JSON_PRETTY_PRINT);

i solved it with my own but it seems my code looks like a spaghetti..... isn't it?

Answer 2

This line seems strange:

foreach($some1 as $merchant_users){

Also clean up some naming to make things more readable:

$merchant_users = DB::table('merchant_users as a')
    ->leftjoin('user_rebate_transactions as b', 'b.user_id','=','a.id') 
    ->select('a.id','a.name',DB::raw('SUM(b.amount) as total'),'a.email')
    ->where('b.status','unpaid')
    ->groupBy('a.id','a.name','a.email')->get();

foreach ($merchant_users as $user) {
    $transactions = DB::table('user_rebate_transactions')->select('*')
        ->where([['user_id','=',$user['id']],['status','=','unpaid']])->get();
    $out['data'][] = ['id' => $user['id'],
        'name' => $user['name'],
        'email' => $users['email'],
        'total' => $users['total'],
        'details' => $transactions,
    ]; 
}

return json_encode($out, JSON_PRETTY_PRINT);