Continents, Countries, Cities and User count

Good evening.

We have the Continent model, the Country model, and the City model.

How can I retrieve the number of users for each country and city with a single query?

// Continent Model
public function countries(): HasMany
{
    return $this->hasMany(Country::class);
}		

// Country Model
public function continent(): BelongsTo
{
	return $this->belongsTo(Continent::class);
}

public function cities(): HasMany
{
	return $this->hasMany(City::class);
}

public function users(): HasMany
{
	return $this->hasMany(User::class);
}
	
// City Model	
public function country(): BelongsTo
{
	return $this->belongsTo(Country::class);
}

public function users(): HasMany
{
	return $this->hasMany(User::class);
}

// My Query
$data = Continent::query()
	//->where('name', '=', 'Europe')
	->with([
		'countries' => function ($query) {
			$query->withCount('users')
				->whereHas('users')
				->orderBy('users_count', 'desc')
				->get();
		},
		'countries.cities' => function ($query) {
			$query->withCount('users')
				->whereHas('users')
				->orderBy('users_count', 'desc')
				->get();
		},
	])
	->orderBy('name', 'asc')
	->get();

Please note that it runs fine on a Mac laptop (with some duplicate queries), but not on Windows...

Any ideas?

thnx!

LaryAI

2 years ago

Level 58

It seems like you're trying to eager load the countries and cities along with the count of users for each. However, there are a few issues with the way you're using with and withCount. You don't need to call get() inside the closure passed to with or withCount. Eloquent will handle the loading of the relationships for you. Here's how you can adjust your query:

$data = Continent::with([
    'countries' => function ($query) {
        $query->withCount('users')
            ->has('users')
            ->orderBy('users_count', 'desc');
    },
    'countries.cities' => function ($query) {
        $query->withCount('users')
            ->has('users')
            ->orderBy('users_count', 'desc');
    },
])
->orderBy('name', 'asc')
->get();

This will give you each continent, with each country and city loaded, and a users_count attribute on each country and city that contains the number of users.

If you're experiencing issues with duplicate queries or performance on different operating systems, it might be related to the configuration of your database or the way Laravel's query caching works. Make sure you're using the same database and version on both your Mac and Windows setups.

Additionally, if you're still having issues, you can debug the queries being run by using the DB::listen method to log queries to the Laravel log:

\DB::listen(function ($query) {
    \Log::info($query->sql, $query->bindings, $query->time);
});

Place this in your AppServiceProvider or right before your query during debugging to see what's happening under the hood. Remember to remove or comment out this code in production environments.

Lastly, ensure that your database indexing is properly set up for the users table, especially on the foreign keys that reference the countries and cities tables, as this can significantly affect query performance.

kevinbui

2 years ago

Level 41

What about calculate the number of users for each city, and that aggregate that results to get the number of users for each country.

$cities = City::withCount('users')
    ->get();

$numberOfUsersPerCountry = $cities->groupBy('country_id')
    ->map(fn ($cities, $countryId) => $cities->sum('users_count'));

If you want countries and cities attached together in a relationship, we can do the followings:

Country::with('cities')
    ->get()
    ->tap(function ($countries) {
        $countries->map->cities->collapse()->loadCount('users')
    })
    ->each(function (Country $country) {
        $country->setAttribute(
            'users_count',
            $country->cities->sum('users_count');
        );
    })

Edited: Giving this a second look, @laryai DB query looks legit, that could be the best solution.

Please or to participate in this conversation.