Create an array containing child elements when querying in laravel-CodePudding

I'm working on laravel. I have 2 mysql tables:

table: Log

id   name    description     status
1   login    login system    success
2   create   create user     success
3   update   update user     success

table: log_detail

id    log_id     old_val        new_val
1       3         admin           user

In controller LogController.php I query :

$list = DB::table('log')
            ->leftJoin('log_detail', 'log.id', '=', 'log_detail.log_id')
            ->selectRaw("log.name, log.description, log.status, log_detail.oldVal, log_detail.newVal")
            ->get()
            ->toArray();

// result

array(11) {
        [0]=>
        object(stdClass)#1377 (3) {
          ["name"]=>
          string(6) "login"
          ["description"]=>
          string(19) "login system"
          ["status"]=>
          string(6) "success"
          ["oldVal"]=> NULL
          ["newVal"]=> NULL
        }
        [1]=>
        object(stdClass)#1377 (3) {
            ["name"]=>
            string(6) "create"
            ["description"]=>
            string(19) "create user"
            ["status"]=>
            string(6) "success"
            ["oldVal"]=> NULL
            ["newVal"]=> NULL
          }
        [2]=>
        object(stdClass)#1377 (3) {
            ["name"]=>
            string(6) "update"
            ["description"]=>
            string(19) "update user"
            ["status"]=>
            string(6) "success"
            ["oldVal"]=>
            string(7) "admin"
            ["newVal"]=>
            string(6) "user"
          }
      }

The result is what I want..Now I want the results in the log_detail table to be in an array. Example output I want:

      object(stdClass)#1377 (3) {
        ["name"]=>
        string(6) "update"
        ["description"]=>
        string(19) "update user"
        ["status"]=>
        string(6) "success"
        ["data"]=> [
            ["oldVal"]=>
            string(6) "admin"
            ["newVal"]=>
            string(6) "user"
        ]
      }

Please give me your opinion. Thank you for reading. Thanks

CodePudding user response：

You can map your results to have the desired output:

$list = DB::table('log')
            ->leftJoin('log_detail', 'log.id', '=', 'log_detail.log_id')
            ->selectRaw("log.name, log.description, log.status, log_detail.oldVal, log_detail.newVal")
            ->get()
            ->toArray();

$mappedList = $list->map(function ($record) {
        $record['data'] = [
                'oldVal' => $record['oldVal'],
                'newVal' => $record['newVal'],
            ];
        unset($record['oldVal'], $record['newVal']);
        
        return $record;
    });

You can read more about it in the documentation.

PS: It would be better for you to use Relationships. That way, you won't need to join your data manually wherever you need to pull log log details.

CodePudding user response：

I would suggest to use Laravel eloquent for this, as eloquent provide similar response.

1st Step creating relations in your models.

// In your Log.php

public function details(){
  return $this->hasMany(Detail::class);
}

// In your Detail.php

public function log(){
  return $this->belongsTo(Log::class);
}

Then using eloquent call records like below

$logs = Log::with('details')->get();

Every log has ->details object in it, can be accessible like below

foreach($logs as $log){
   dump($log->details); // you'll see all the details table columns here under attribute
}

Laravel docs: https://laravel.com/docs/9.x/eloquent-relationships#one-to-many

Follow this doc for creating relations in laravel eloquent.