@oronatur Aha! Now we’re getting somewhere! Part of the confusion stems from your use of the word level, which doesn’t mean what I think you’re using it to mean (number, amount, sum total). Another part is that your database structure is very inefficient and hard to understand without seeing the actual data.
So, here’s what I understand now:
-
buildings holds the general information about building types, how much they cost to build, etc.
-
building_levels holds a list of how many of each type of building a player has actually built
So the only thing building_levels really does is connect players to buildings, with an additional counter. Each player can build multiple types of buildings (if they have the money/rights to, of course), and each type of building can be built by multiple players. That is a many-to-many relationship, and building_levels is a pivot table!
Pivot tables are essential in databases, and they are fundamental to many-to-many relationships in Laravel, which represents them as relationships between models. But the way you’ve set it up breaks how pivot tables work.
Below is how you would do it the Laravel way. Most of the work is restructuring your tables to make more sense, because your current structure is not very efficient and certainly not easy to select from using Eloquent.
Make a player model + table
Your model should be called Player and your table should be players. This will contain all the details about the user – name, nickname, avatar, e-mail/username, player level, etc. It should have an ID column called just id.
Make a building model + table
This is the Building model and buildings table that you already have. If I understand you correctly, you currently only have four types of buildings available (palaces, barracks, libraries and granaries – making for a very odd town!), but I assume more could be added later on.
I don’t think you need to change anything in your buildings table, except I would change all the column names by removing the building_ part at the beginning – it’s obvious that things in the buildings table relate to buildings – and rename max_level to something like max_count or max_per_user, since I’m guessing that refers to how many of this type of building a user can build.
This table also needs an ID column just called id.
Make a pivot table
Your pivot table should be named building_player (not building_level!) for Laravel’s auto-features to work. It should have the following columns:
- player_id
- building_id
- count
In this table, you can have multiple rows per player – one row for each building type. This is the main difference from your current table structure: instead of a single row per user containing the count of all the different building types, here we have one row per building type per user, containing only the building type’s ID (not name) and how many of that building type this user has made.
Your migration to create the table would look like this:
public function up() {
Schema::create('building_player', function (Blueprint $table) {
$table->foreignId('player_id')->constrained();
$table->foreignId('building_id')->constrained();
$table->integer('count');
$table->unique(['player_id', 'building_id']);
});
}
public function down() {
Schema::dropIfExists('building_player');
}
(The ID columns in your player and building tables should also be created using $table->id() for the foreign keys to work.)
Load your players’ relationships
What you’re after is an overview of the buildings made by a player – that is essentially what model relationships do. A player can have a number of building types associated with them, which are loaded as properties on the player model.
As mentioned above, your relationship here is a many-to-many relation (each player can related to many types of building, each building type can relate to many users). The way the tables have been set up above is intended to make Laravel’s many-to-many relationship functions work as easily as possible.
When you make a many-to-many relationship on your model, the model keys in the pivot table (player_id and building_id) are automatically brought in, but you have to specify if there are any additional columns you want to include as well (in this case, count).
In your player model, add a relationship for buildings:
// Player.php
public function buildings() {
return $this->belongsToMany(Building::class)->withPivot('count');
}
And that’s pretty much it! You can now load any player’s buildings from anywhere. For example, let’s say you want an overview of player 123’s buildings. You have a PlayerController used to control data about players and pass it on to views that define how to output that data, and in that controller, you’d have a function that specifically fetches building info. You would make a route along these lines:
// web.php
Route::get('players/{player}/buildings', [PlayerController::class, 'buildings'])->name('player.buildings');
In your controller, this would be the relevant function – super simple:
// PlayerController.php
public function buildings(Player $player) {
$player->load('buildings');
return view('players.buildings', compact('player'));
}
The player model is automatically resolved by the service provider based on the {player} placeholder in the route representing the ID (here 123). Then we load the buildings() relationship by doing ->load('buildings').
The result is a model where $player->buildings is a collection of Building objects, each with a pivot property that contains a count property. So in your view, you can simply do this:
// players/buildings.blade.php
<section class="player-listing">
<h1>All {{ $player->name }}’s buildings</h1>
@foreach ($player->buildings as $building)
<div class="building">
<h2>{{ $building->name }}</h2>
<p>Number of buildings: {{ $building->pivot->count }}</p>
</div>
@endforeach
</section>
