Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

PFrank's avatar

toArray() merged

I have three existing tables, namely city, state and country as follows:

table city: id | name | state_id --> 1 Portland 2, 2 Portland 1

table state: id | name | country_id --> 1 Maine 1, 2 Oregon 1

table country: --> id | name 1 USA, 2 Canada

I need to merge following 3 toArray to show up in one line.

function getCityStateCountry()
{
    $city = City::orderBy('name')->pluck('name', 'id')->toArray(); // Portland
    $state = State::orderBy('name')->pluck('name', 'id')->toArray(); // Oregon
    $country = Country::orderBy('name')->pluck('name', 'id')->toArray(); // USA
    return $cityStateCountry ?? // display as Portland, Oregon, USA
}

Which is the fastest way to achieve $cityStateCountry in Laravel 9, to display query result display as Portland, Oregon, USA?

Thank you in advance

0 likes
17 replies
LaryAI's avatar
LaryAI
AI
3 years ago
Level 58

To achieve the desired output, you can use Laravel's Eloquent relationships to join the tables and retrieve the required data. Here's an example code snippet:

function getCityStateCountry()
{
    $cityStateCountry = City::with(['state.country'])
        ->orderBy('name')
        ->get()
        ->map(function ($city) {
            return $city->name . ', ' . $city->state->name . ', ' . $city->state->country->name;
        })
        ->implode(', ');

    return $cityStateCountry;
}

In this code, we're using the with method to eager load the related state and country models. Then, we're using the map method to format the data as required. Finally, we're using the implode method to join the formatted data with commas.

Note that this code assumes that you have defined the appropriate relationships in your City, State, and Country models. For example, in your City model, you would define a state relationship like this:

public function state()
{
    return $this->belongsTo(State::class);
}

Similarly, in your State model, you would define a country relationship like this:

public function country()
{
    return $this->belongsTo(Country::class);
}
PFrank's avatar

@LaryAI ,

Thanks for the prompt answer. Unfortunately, it is not working for me. However, I'll try to think of something based on your answer.

Best

MohamedTammam's avatar

Make sure you have the relationship in the models.

$countries = Countries::with('states', fn($q) => $q->with('cities'))->get();

@foreach($countries as $country)
	@foreach($country->cities as $city)
		@foreach($city->states as $state)
			<tr>
				<!-- You have here $country, $city and $state -->
			</tr>
		@endforeach
	@endforeach
@endforeach
PFrank's avatar

@MohamedTammam

Hi, Thanks.

In fact I can get..

city, state ( as city model has the relationship )

state, country ( as state model has the relationship )

But, unable merge city, state, country ( as city model has no direct relationship to country ).

And it has to be in a single helper function cityStateCountry.

MohamedTammam's avatar

From the country you get the states, from the states you get the city. As I mentioned in the code.

tykus's avatar

You can achieve this with a single SQL query

SELECT GROUP_CONCAT(cities.name, ', ', states.name, ', ', countries.name) as address 
FROM cities
LEFT JOIN states ON state_id = states.id
LEFT JOIN countries ON country_id = countries.id
GROUP BY cities.id

Or, as Query Builder

DB::table('cities')
    ->selectRaw("GROUP_CONCAT(cities.name, ', ', states.name, ', ', countries.name) as address")
    ->leftJoin('states', 'state_id', 'states.id')
    ->leftJoin('countries', 'country_id', 'countries.id')
    ->groupBy('cities.id')
    ->get();
PFrank's avatar

@tykus

Thanks. But how would you convert it to city, state, country array?

tykus's avatar
tykus
3 years ago
Best Answer
Level 104

@PFrank what should the array look like?

Does this produce the desired result?

DB::table('cities')
    ->selectRaw("GROUP_CONCAT(cities.name, ', ', states.name, ', ', countries.name) as address")
    ->leftJoin('states', 'state_id', 'states.id')
    ->leftJoin('countries', 'country_id', 'countries.id')
    ->groupBy('cities.id')
    ->pluck('address')
    ->all()
PFrank's avatar

@tykus

Thanks a million.

Your last reply works for me.

Stay Blessed!

All the Best.

Snapey's avatar

you cant convert this data to a simple array unless ALL the records are related. You question gives no indication that they are.

Also, you cannot create a flat array when some models have more rows than others

PFrank's avatar

Thank you so much to all of you for replying.

Just an advice to anyone who is going to use the above builder. Make sure your tables cities, states & countries have Soft Deletes. Otherwise accidental delete ( permanent ) of any city can change all id numbers if transferred / saved using the above builder.

All the Best.

PFrank's avatar

@tykus

Sorry, if misunderstood. Nothing wrong with your code. It is 100% perfect.

PFrank's avatar

I have improved the code by @tykus as follows to get query results by keyword search. Please customise according to your needs.

if ( strlen($request->name) > 2) {
DB::table('cities')
    ->selectRaw("GROUP_CONCAT(cities.name, ', ', states.name, ', ', countries.name) as address")
    ->where('cities.name','LIKE',$request->name.'%')
    ->leftJoin('states', 'state_id', 'states.id')
    ->leftJoin('countries', 'country_id', 'countries.id')
    ->groupBy('cities.id')
    ->pluck('address')
    ->all()
 }

Works pretty fast with keyword input search.

~~~

Please or to participate in this conversation.