Ad

Lumen/Laravel Query Builder 2D Array

Been going at this for a few hours and cant seem to get it right.

Basically I have the below sql I'm moving over to Lumen for my API

$data = array();
$query = "SELECT dept.name AS dept_name, r.id AS id, r.name AS role_name FROM careers_dept AS dept INNER JOIN careers_roles AS r ON dept.id = r.dept_id";

$result = mysqli_query($connection, $query);
if($result) {
    while($row = mysqli_fetch_assoc($result)) {
        // read into 2D array
        $row['seoName'] = seoUrl($row['role_name']);
        $data[$row['dept_name']][] = $row;
    }
    $response = json_encode($data);
}
return $response;

This is what I have so far;

$roles = DB::connection('careers')->table('careers_dept')
    ->join('careers_roles', 'careers_roles.dept_id', '=', 'careers_dept.id')
    ->select('careers_roles.id', 'careers_roles.name', 'careers_dept.name')
    ->get();

foreach ($roles as $role) {
    $role->seo = $this->seoUrl($role->name);
}

$data['data'] = $roles;
$data['success'] = true;

$response = json_encode($data);
return $response;

For starters, i can't figure out how to access the elements of the array correctly like in this line

$row['seoName'] = seoUrl($row['role_name']);

Both tables have name as a column but with normal sql you can use AS to give an alias which is how I did with normal sql.

And then this line is also something I can't work out how to convert it. The below line creates the 2D array making dept_name a parent array.

$data[$row['dept_name']][] = $row;

I also loop through the array to get an attribute, transform it using a private function and then inject it back in

foreach ($roles as $role) {
    $role->seo = $this->seoUrl($role->name);
}

This does work but I'm grabbing the wrong name attribute because both tables have a column with the same name.

Ad

Answer

You can use AS in Laravel query builder as well:

 ->select(
      'careers_roles.id', 
      'careers_roles.name as role_name', 
      'careers_dept.name as dept_name'
 )

And you can group results by dept_name with groupBy method of Collection class (https://laravel.com/docs/5.1/collections#method-groupby).

So your final code could look like that:

$roles = DB::connection('careers')
    ->table('careers_dept')
    ->join('careers_roles', 'careers_roles.dept_id', '=', 'careers_dept.id')
    ->select(
         'careers_roles.id', 
         'careers_roles.name as role_name', 
         'careers_dept.name as dept_name'
    )
    ->get();

foreach ($roles as $role) {
    $role->seo = $this->seoUrl($role->role_name);
}

//convert into collection, group by "dept_name" key and then convert back into array
$data = collect($roles)->groupBy('dept_name')->all();
Ad
source: stackoverflow.com
Ad