How do I join multiple tables and use data from one table to make columns for the output?

user_17835677 0
Viewed 43

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.

laravel mysql
1 Answers

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
  }
]
user_43394020
Related