Hierarchy database

$countries = Country::all();

When a country is selected

$cities = City::where('country_id', $countryId)->get();

And when city is selected

$villages = Village::where('country_id', $countryId)
				->where('city_id', $cityId)->get();

Of course that's right, but the point is that I want a sequence like this in json:

        name: 'Country', id:'1' children: [
            {
                text: 'city1', id:'1-1' children: [
                    {
                        id: '1-1-2', name: 'village1'
                    },
                    {
                        id: '1-1-3', name: 'village2'
                    }
                ]
            }
         ]
     }

that's my problem i can't see how i do it

@umbus Well what have you tried? You’ll need to fetch all countries and eager-load cities and villages. But if you have a lot of rows then this may be inefficient to send all that data to the client.

i don't know what exacly you want this for but you can use map like follow maybe it can be speed up a little bit but to give you a general idea... any how...

    $country = Country::first();
    $city = City::where('country_id',$country->id)->first();
    $villages = Village::where('city_id',$city->id)->get();
    return $villages->map(function($village){
        $ct = $village->city;
        $cnty = $ct->country;
            return collect([
                'name' => $cnty->name,
                'id' => $cnty->id,
                'children' => [
                    'text' => $ct->name,
                    'id' => $cnty->id.'-'.$ct->id,
                    'children' => [
                        'id' => $cnty->id.'-'.$ct->id.'-'.$village->id,
                    'name' => $village->name
                    ]
                ]
            ]);
        });

the above will give the result you are looking for more or less...

[
        {
            "name": "United States Minor Outlying Islands",
            "id": 1,
            "children": {
                "text": "East Carleeton",
                "id": "1-1",
                "children": {
                    "id": "1-1-1",
                    "name": "Hermann Islands"
                }
            }
        },
        {
            "name": "United States Minor Outlying Islands",
            "id": 1,
            "children": {
                "text": "East Carleeton",
                "id": "1-1",
                "children": {
                    "id": "1-1-2",
                    "name": "Kareem Springs"
                    }
            }   
        },
    ];

@umbus as i said i don't know, what exactly you are trying to accomplish here, my solution shows how to format your required response. Now if you want to make it conditional that is totally up to you and how many conditions you have but instead of ifs you could try when and inspect the request instead.

//for instance
$query = Village::query();

$query->when(request('filter') == 'name', function ($q) {
    return $q->where('name', 'like', "%request('name')%");
});
//other conditions
$query->get()->map(...)

but you do need to consider what @martinbean said, and optimize how you wann fetch the end response.

I apologize for my English, I will try to explain it better. What I want to do is exactly this:

https://www.nicesnippets.com/blog/laravel-8-dynamic-autocomplete-search-with-select2-tutorial

But the result that appears would be for example: (if search Ann - allaways 3 chars or more) USA -> Massachusetts -> Annisquam [option value 1-2-45] ... (if search USA) USA [option value 1] ...

(if search setts) USA -> Massachusetts [option value 1-2] USA -> Massachusetts -> Mattapoisett [option value 1-2-78] ...

Thank you for your patience and help

@umbus ok so you want three dependent or cascading select2 boxes. here is a gist with live demo and in the comments there is a situation for your use case.

https://gist.github.com/ajaxray/187e7c9a00666a7ffff52a8a69b8bf31

The example you gave me are two select boxes with hierarchy. I just want a select box with everything :/

@umbus consider the following route which provides the data, structured as select2 requires and i assume you have setup your relationships properly.

Route::get('/villages', function () {
	return collect(['results'=>App\Models\Country::get()->map(function($country){
        return collect(
            [
                'text' => $country->name,
                'children' => 
                    $country->cities->map(function($city){
                        return collect([
                            'id' => $city->id,
                            'text' => $city->name,
                            'children' => $city->villages->map(function($village){
                                return collect([
                                    'id' => $village->id,
                                    'text' => $village->name
                                ]);
                            })
                        ]);
                    })
                
            ]
        );
    }),'pagination' => ['more' => false]])->toJson();
});

and in your view somewhere you have initialized a select as following example:

		<select class="umbus-hierarchy"></select>

		$('.umbus-hierarchy').select2({
            ajax: {
                url: '/villages',
                dataType: 'json'
            },
            width: '100%',
            placeholder: '- Select -'
        });

the above example will work as you wanted. but according to the select2 library https://select2.org/options#hierarchical-options nesting more than 2 level is not supported. suppose if a city doesn't have a village you won't be able to select that city and same goes for country with a city and no village. because only the village is selectable... ~cheers

I ended up using several selectboxs with hierarchy, I thought it would be less time consuming and simpler.

Thank you for your help