I have three tables called countries, states & cities.

countries table schema:

id
country_name

states table schema:

  id
  state_name
  country_id

cities table schema:

  id
  city_name
  state_id
  country_id

Now I need to output like :

{
country_name: United States,
states counts of all states in the country,
cities: counts of all cities in the country
}

CodePudding user response：

      select cc.country_name , 
             count (distinct s.id) count_states ,
             count(distinct c.id)  count_cities
  from Cities c 
  inner join states s on s.id = c.state_id
  inner join countries cc on cc.id = s.country_id

My way: simply join all and use distinct to count the state and city ids

CodePudding user response：

In Controller

Countries::withCount(['states','cities'])->get();

In Country Model

public function states()
{
    return $this->hasMany(States::class, 'country_id', 'id');
}
 public function cities()
{
    return $this->hasMany(Cities::class, 'country_id', 'id');
}