Merging 3 collections to return one object per row

I'm returning a town, state and country to a Vue 2 autocomplete field using axios. The fields for the queries are all in the same table; locations. If I return just the town, the field works great but there are plenty of towns in the world with the same name so I want to return the state/region and country the town is in along with it. The problem I have is getting my controller to output an object for the town, state and country instead of merging them into separate objects.

I've tried using the merge, put and combine methods that Eloquent provides. Merge and put will give me an array of 6 objects with my test data which is Birmingham as there's one in West Midlands, England and one in Alabama, USA. Objects 0 and 1 have the town id, town name and parent id, 2 and 3 have the state id etc and 4 and 5 have the country id etc. Searched Google and here for answers with most saying merge but it won't actually put each separate collection into one object.

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use \App\Location;

class AjaxLocationsController extends Controller {
public function index(Request $request) {
$searchquery = $request->searchquery;
$states = collect();
$countries = collect();
$all_details = collect();

    $locations = Location::select('id', 'location', 'parent_id')
    ->where([['id', '>', 4351], ['location', 'like', '%' . $searchquery . '%']])
    ->orderBy('location')
    ->get();

    foreach($locations as $location)
    {

        $state = Location::select('id as state_id', 'location as state', 'parent_id as state_parent_id')
        ->where('id', '=', $location->parent_id)
        ->get();

        $states->push($state);
    }

    $states = $states->flatten();

    foreach($states as $state)
    {
        $country = Location::select('id as country_id', 'location as country')
        ->where('id', '=', $state->state_parent_id)
        ->get();

        $countries->push($country);
    }

    $countries = $countries->flatten();

    $all_details = $all_details->merge($locations);
    $all_details = $all_details->merge($states);
    $all_details = $all_details->merge($countries);
    $all_details = $all_details->flatten();

    return $all_details;
}

}

I would've expected this to be pretty easy to do and although I'm pretty new to Laravel (5.8), I've had a lot of experience using PHP arrays. I'm thinking I must be missing something as I've been at this all day!

Any help would be much appreciated.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2zmIOwC
via IFTTT