How to get the reverse of a dependent dropdown when editing a child model

So, I have a dependent dropdown used in a lot of places on my site that allows to define country->division(state)->city made in livewire.

This works great when going downwards, But i have found when trying to edit the city, the state and country values are no longer present and I'm not entirely sure how to rectify this, as by default, the state value is null until a country value is selected. The city value would be an input field while the others are dropdown. My 3 tables are set up so each city only references the State_id, and then each state to the country_id

I tried calling the component like this in the City edit.

@livewire('common.locations', ['city'=>$city])

Tried adding a hasOneThrough relationship in the City model, public function CityCountry () { return $this->hasOneThrough(Country::class, State::class); } I also reversed the two relations, neither worked.

but thats getting column not found errors: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'states.city_id' in 'field list' (SQL: select countries.*, states.city_id as laravel_through_key from countries inner join states on states.id = countries.state_id where states.city_id = 852 and countries.deleted_at is null and states.deleted_at is null limit 1) "

In the livewire component, I wasnt sure how to structure a method, but i did alter my mount function as follows:

 public $countries;
    public $divisions;
    public $division_name;
    public $cities;
    public $name;
    public $selectedCountry = null;

    public $selectedState = null;
    public $selectedCity  = null;

   public function mount (City $city)
    {
        $this->countries = Country::with('media')->get();
        $this->divisions = collect();
        //        $this->selectedState   = $city->state_id;
        //        $this->selectedCountry = $city->CityCountry->id;


    }
    public function updatedSelectedCountry ($value)
    {
        $this->divisions     = State::where('country_id', $value)->get();
        $this->selectedState = null;
        $this->division_name = $this->countries->where('id', $this->selectedCountry)->first()->division_name;
    }

The commented lines were what I attempted.

SilenceBringer

4 years ago

Best Answer

Level 55

@jcc5018 pay attention to relationship declaration https://laravel.com/docs/8.x/eloquent-relationships#has-one-through you need to specify keys explicitly in your case

public function CityCountry () {
	return $this->hasOneThrough(
		Country::class,
		State::class,
		'id',
		'id',
		'state_id', // assuming cities table has `state_id` foreign key
		'country_id' // assuming states table has `country_id` field
	);
}

jcc5018

4 years ago

Level 8

Thanks, I was able to get the country id from this and it displays, but I'm still having trouble with the state selection.

I've tried

                'selectedState'=>$city->state_id])

and this in mount:

 public function mount (City $city)
    {
        $this->countries = Country::with('media')->get();
        $this->divisions = collect();

        $this->selectedCountry = $city->CityCountry->id;
     //   $this->selectedState   = $city->state_id;

    }

Am i passing the variable correctly for selectedState?

Even though the country is now being displayed, it is not displaying the state list for that country. I guess because the update hasnt been triggered?

SilenceBringer

4 years ago

Level 55

@jcc5018 your divisions are empty. as example - you can load only state assocaited with the city

 public function mount (City $city)
    {
        $this->countries = Country::with('media')->get();
        $this->divisions = State::where('id', $city->state_id)->get();

        $this->selectedCountry = $city->CityCountry->id;
        $this->selectedState   = $city->state_id;

    }

jcc5018

4 years ago

Level 8

@SilenceBringer

Thanks, changed it a little to get all the states per country, but you got me there.

   {
       $this->countries = Country::get();
       //        $this->divisions = collect();
       $this->divisions       = State::where('country_id', $city->CityCountry->id)->get(['name', 'id']);
       $this->selectedCountry = $city->CityCountry->id ?? null;
       $this->selectedState   = $city->CityState->id ?? null;

   }

Please or to participate in this conversation.