Making a tree with MySQL datas-CodePudding

I have a MySQL table like this:

 ------ ----------------- --------- ------------ 
| id   | name            | refferal| reference  |
 ------ ----------------- --------- ------------ 
|    1 | Alex Muller     |       1 |       null |
|    2 | John Doe        |       2 |          1 |
|    3 | Tom Foe         |       3 |          1 |
|    4 | Harry Pott      |       4 |          3 |
|    5 | Kate Garry      |       5 |          3 |
|    6 | Mike Blue       |       6 |          4 |
 ------ ----------------- --------- ------------

(more data than this...)

I need to turn that data to JSON file with Laravel. Like this:

[{"id":1,"name":"Alex Muller","parent":0},
{"id":2,"name":"John Doe","parent":1},
{"id":3,"name":"Tom Foe","parent":1},
{"id":4,"name":"Harry Pott","parent":3},
{"id":5,"name":"Kate Garry","parent":3},
{"id":6,"name":"Mike Blue","parent":4}]

At the and of this I will get a tree view like this:

TREE

I just made this json file with my own write. And I don't know what to do. I'm waiting your answers. Thank you.

CodePudding user response：

On the model get the keys you need and then with map() change the key reference to parent, on that check if is null the reference to put a 0 then encode the array for json with json_encode.

$array = Model::get(['id', 'name', 'reference'])
   ->map(function($model){
        return [
           'id' => $model->id,
           'name' => $model->name,
           'parent' => is_null($reference->reference)? 0 : $reference->reference,
        ];
    })
    ->toArray();

Then just make a json with that array:

echo json_encode($array);

CodePudding user response：

If you want to add extra fields like "parent" in your example you can use map on the collection:

 $users = User::where(function($query){
  ...
 })->get()->map(function($user){
   return array(
     "id"  => $user->id,
     "name" => $user->name,
     "parent" => *INTEGER*
   );
 })->toJson();

Or if you just want to encode the model attributes, you can use toJson serialization directly with the collection:

$users = User::User::where(function($query){
  ...
 })->get()->toJson();

for further information, refer to the links:

https://laravel.com/docs/collections

https://laravel.com/docs/eloquent-serialization#serializing-to-json