Dynamic dependency of select boxes via Ajax on Laravel but getting data through different tables, how to?

Question

I'm trying to have a group of 3 select boxes in a form that are dependant from the one above, in the Country -> State -> Municipality way.

I'm following this tutorial that successfully fetches data and populatates the first select box (Country in this case).

The thing is that in that tutorial the data is inside a single table, while in my application it's in multiple tables.

I'm wondering how could I fetch data across those multiple tables that correspond to the id of the selected item in the select box above? But in the LARAVEL way?

My HTML (all based on the tutorial linked above):

 {{-- Muestra los estados sacados de la base de datos. --}}
                        <div id="inputs-estado">
                            <div class="form-group">
                                {{-- Estados --}}
                                <label for="">Estado</label>
                                <select name="state" id="state" class="form-control dynamic" data-dependant="state">
                                    @foreach ($estados as $estado)
                                    <option value="{{ $estado->state }}">{{ $estado->state }}</option>
                                    @endforeach
                                </select>
                                <br>
                                {{-- Municipio/Delegación --}}
                                <label for="">Ciudad</label>
                                <select name="state" id="state" class="form-control dynamic" data-dependant="city">
                                    <option value="">Selecciona la ciudad</option>
                                </select>
                                <br>
                                {{-- Colonia --}}
                                <label for="">Municipo</label>
                                <select name="state" id="state" class="form-control dynamic" data-dependant="municipality">
                                    <option value="">Selecciona el municipio</option>
                                </select>

                            </div>
                        </div>

JS:

formDynamic.change(function () {
    if ($(this).val() != '') {
        let select = $(this).attr('id');
        let value = $(this).val();
        let dependent = $(this).data('dependent');
        let _token = $('input[name="_token"]').val();

        $.ajax({
            url: "{{ route('postscontroller.fetch') }}",
            method: "POST",
            data: {
                select: select,
                value: value,
                _token: _token,
                dependent: dependent
            },
            success: function (result) {
                $('#' + dependent).html(result);
            }
        })
    }
});

Controller:

public function create()
{

    // Toma los estados de la base de datos.
    $estados = DB::connection('db_postalcodes')
        ->table('state')
        ->groupBy('state')
        ->get();

    // El with hace que se adjunten variables al view.
    return view('admin.posts.create')->with('estados', $estados);
}

public function fetch(Request $request)
{
    $state_id = DB::connection('db_postalcodes')->table('city')->get();
    $select = $request->get('select');
    $value = $request->get('value');
    $dependent = $request->get('dependent');
    $data = DB::connection('db_postalcodes')
        ->table('city')
        ->where($select, $state_id->state_id)
        ->groupBy($dependent)
        ->get();

        $output = '<option value="">Select '.ucfirst($dependent).'</option>';

        foreach($data as $row){
            $output .= '<option value="'.$row->$dependent.'">'.$row->$dependent.'</option>';
        }

        echo $output;
}

Routes.php

Route::group(['prefix' => 'admin', 'namespace' => 'Admin', 'middleware' => 'auth'], function () {

    Route::get('/', 'AdminController@index')->name('admin');    
    Route::get('posts', 'PostsController@index')->name('admin.posts.index');
    Route::get('posts/create', 'PostsController@create')->name('admin.posts.create');
    Route::post('posts/create', 'PostsController@fetch')->name('postscontroller.fetch');
    Route::post('posts', 'PostsController@store')->name('admin.posts.store');
});

My tables:

Answer 1

Laravel's model and relationships can help greatly here. Especially hasManyThrough. Check out the Docs for more in depth explanation.

You'll need three models: Country, State, and Municipality. You can use artisan to make these via php artisan make:model modelName or create them in your project manually. Either way it should look like:

Country Model

use Illuminate\Database\Eloquent\Model;

class Country extends Model {

  // A Country can have many Municipalities but they do not directly belong
  // to the Country they belong to the State -- Which belongs to the Country
  public function municipalities() {
    return $this->hasManyThrough('App\Municipality', 'App\State');
  }

  // Each Country can have many States
  public function states() {
    return $this->hasMany('App\State');
  }

}

State Model

use Illuminate\Database\Eloquent\Model;

class State extends Model {

  // Assuming each State can only belong to One Country
  public function country() {
    return $this->belongsTo('App\Country');
  }

  // Each State can have many Municipalities
  public function municipalities() {
    return $this->hasMany('App\Municipalities');
  }

}

Municipality Model

use Illuminate\Database\Eloquent\Model;

class Municipality extends Model {

  // Assuming each Municipality can belong to only one State
  public function state() {
    return $this->belongsTo('App\State');
  }

  // Should you ever need this Municipality's Country
  public function country() {
    return $this->state->country;
  }

}

These all work under the assumption you have a table structure similar to this:

countries:

| id | name | another_column |
-----------------------------
  1  | USA  |

states:

| id | country_id | name | another_col |
----------------------------------------
  1  |      1     |  OK  |

municipalities:

| id | state_id | postalcode_id | name | another_col |
------------------------------------------------------
  1  |    1     |       1       | OKC  |

postal_code:

| id | state_id | postal_code |
-------------------------------
  1  |     1    |   73102     |

As for your controller you can break it into 3 endpoints: getCountries, getStatesByCountry, getCitiesByState - each one gets data based on an ID passed to it.

public function getCountries(Request $request) {
  $id = $request->get('id');
  if ( $id ) {
    // Or return some string you want to return
    return response()->json(Country::find($id));
  }
  $countries = Country::all();
  // or loop over all $countries and make a string
  return response()->json($countries);
}

public function getStatesByCountry(Request $request) {
  $id = $request->get('country_id');
  return response()->json(Country::find($id)->states);
  // Or
  // return response()->json(State::where('country_id', '=', $id)->get());
}

public function getCitiesByState(Request $request) {
  $id = $request->get('state_id');
  return response()->json(State::find($id)->municipalities);
  // or return response()->json(Municipality::where('state_id', '=', $id)->get());
}

Each time one of your dynamic options change you would request one step lower. So if you change country you would request getStatesByCountry - if state changes you would request getCitiesByState.

Lastly if you want all cities by country

public function getCitiesByCountry(Request $request) {
  $id = $request->get('country_id');
  return response()->json(Country::find($id)->municipalities);
}

Edit

You would place each of these functions in your controller to handle the requests. You'll also need to update your routes, web.php, and add a route and handler for each of the functions.

// {id?} signifies an optional parameter. Based on the func. passing
// no ID gets all Countries - specifying one only gets the one.
Route::get('/posts/get-countries/{id?}', 'PostController@getCountries');
Route::get('/posts/get-states-by-country/{id}', 'PostController@getStatesByCountry');
Route::get('/posts/get-cities-by-state/{id}', 'PostController@getCitiesByState');