I am building an error tracking system using Laravel and MySQL so far I have 3 tables

• fillers as f: Holds the fillers id, first, and last name columns:
  • id,= el.tech,
  • first_name,
  • last_name
• error_type as et: Holds a table to describe the types of possible errors and assigns an id columns:
  • id,
  • name = el.error_id
• error_logs as el: Holds all errors for all fillers columns:
  • id,
  • error_id, =et.name
  • tech, =f.id

public function countAllLogs()
{
    $errorLog = DB::table('fillers AS f')
        ->select(
            'f.id as id',
            'f.first_name as first_name',
            'f.last_name as last_name',
            // 'f.location as location',
            'et.name as error_type',
        )
        ->selectRaw(
            'count(el.error_id) as count', 
        )
        ->groupBy('f.id')
        ->groupBy('el.error_id')
        ->leftjoin('error_logs as el', 'f.id', '=', 'el.tech')
        ->leftjoin('error_types as et', 'et.id', '=', 'el.error_id')
        ->get();

    return response($errorLog, 200);
}

using Postman Get function gives the following example

    {
        "id": 59,
        "first_name": "Steve",
        "last_name": "Martian",
        "error_type": "ndc",
        "count": 3
    },
    {
        "id": 59,
        "first_name": "Steve",
        "last_name": "Martian",
        "error_type": "jumper",
        "count": 1
    }

That is almost what I want but it separates out based on the "error_type." I have seen "pivot" but am unsure how to implement this with what I have.

The desired output would be

    {
        "id": 59,
        "first_name": "Steve",
        "last_name": "Martian",
        "jumper": 1,
        "ndc": 3
    }

Thanks.

CodePudding user response：

You can achieve this with the flatMap collection method. I'm assuming multiple ids will be present, so groupBy and map will also be used.

$errorLog = DB::table('fillers AS f')->...->get();

$json = $errorLog
    ->groupBy('id')
    ->map->flatMap(fn($item) => [
        'id' => $item->id,
        'first_name' => $item->first_name,
        'last_name' => $item->last_name,
        $item->error_type => $item->count
    ])
    ->values()
    ->toJson();

return response($json, 200);

Explanation

Here's the original collection. I've added a couple of entries to demonstrate different ids.

array:4 [
  0 => {#4540
     "id": 59
     "first_name": "Steve"
     "last_name": "Martian"
     "error_type": "ndc"
     "count": 3
  }
  1 => {#4567
     "id": 59
     "first_name": "Steve"
     "last_name": "Martian"
     "error_type": "jumper"
     "count": 1
  }
  2 => {#4569
     "id": 57
     "first_name": "qwer"
     "last_name": "Martian"
     "error_type": "ndc"
     "count": 3
  }
  3 => {#4573
     "id": 58
     "first_name": "asdf"
     "last_name": "Martian"
     "error_type": "jumper"
     "count": 1
  }
]

groupBy('id'): Groups the collection by id. This is important for the next step.

array:3 [
  59 => Illuminate\Support\Collection {#4592
    #items: array:2 [
      0 => {#4540
         "id": 59
         "first_name": "Steve"
         "last_name": "Martian"
         "error_type": "ndc"
         "count": 3
      }
      1 => {#4567
         "id": 59
         "first_name": "Steve"
         "last_name": "Martian"
         "error_type": "jumper"
         "count": 1
      }
    ]
  }
  57 => Illuminate\Support\Collection {#4557
    #items: array:1 [
      0 => {#4569
         "id": 57
         "first_name": "qwer"
         "last_name": "Martian"
         "error_type": "ndc"
         "count": 3
      }
    ]
  }
  58 => Illuminate\Support\Collection {#4591
    #items: array:1 [
      0 => {#4573
         "id": 58
         "first_name": "asdf"
         "last_name": "Martian"
         "error_type": "jumper"
         "count": 1
      }
    ]
  }
]

flatMap(Closure): maps the collection and then collapses it. Same as map(Closure)->collapse().

map->flatMap(fn($item) => [...]): Shorthand for

map(function ($grouped) {
    return $grouped->flatMap(function ($item) {
        return [...];
    });
});

This notation is possible thanks to high-order collection methods.

If the groupBy step was not done, then you'd end up with a single item, data completely mixed together.

Here's how the Collection looks like after map->flatMap(Closure):

array:3 [
  59 => Illuminate\Support\Collection {#4590
    #items: array:5 [
      "id" => 59
      "first_name" => "Steve"
      "last_name" => "Martian"
      "ndc" => 3
      "jumper" => 1
    ]
  }
  57 => Illuminate\Support\Collection {#4570
    #items: array:4 [
      "id" => 57
      "first_name" => "qwer"
      "last_name" => "Martian"
      "ndc" => 3
    ]
  }
  58 => Illuminate\Support\Collection {#4588
    #items: array:4 [
      "id" => 58
      "first_name" => "asdf"
      "last_name" => "Martian"
      "jumper" => 1
    ]
  }
]

values(): Discard the array keys. This is important because toJson() would produce a json object instead of a json array if it tried to conserve the keys.

You can see that for yourself with a simple experiment: Compare the results between

• json_encode([ ['a' => 2, 'b' => 3] ]);
• json_encode([1 => ['a' => 2, 'b' => 3] ]);

array:3 [
  0 => Illuminate\Support\Collection {#4590
    #items: array:5 [
      "id" => 59
      "first_name" => "Steve"
      "last_name" => "Martian"
      "ndc" => 3
      "jumper" => 1
    ]
  }
  1 => Illuminate\Support\Collection {#4570
    #items: array:4 [
      "id" => 57
      "first_name" => "qwer"
      "last_name" => "Martian"
      "ndc" => 3
    ]
  }
  2 => Illuminate\Support\Collection {#4588
    #items: array:4 [
      "id" => 58
      "first_name" => "asdf"
      "last_name" => "Martian"
      "jumper" => 1
    ]
  }
]

toJson(): Transforms collection into json string: (I've added line breaks for better readability)

[
  {
    "id":59,
    "first_name":"Steve",
    "last_name":"Martian",
    "ndc":3,
    "jumper":1
  },
  {
    "id":57,
    "first_name":"qwer",
    "last_name":"Martian",
    "ndc":3
  },
  {
    "id":58,
    "first_name":"asdf",
    "last_name":"Martian",
    "jumper":1
  }
]