The resources table contains the user's resources(there are 7 resources for every location) at a certain location. location_supplies contains the amount of resources that are still available to mine on the planet. (for every resource at a location you have one entry)
Now I want to get all of the resources at a location with location_id 1. But in addition, I want to have the resource supply of each resource, so I can access it like this:
I don't know how the relationships between your models, but it should be Many-to-Many between Location and Resource. They should be conected via a pivot table which contains the amount.
class Resource ...
public function locations()
{
return $this->belongsToMany('Location', 'location_supplies')
->withPivot('amout');
}
Just reverse it for the Location model.
The pivot data is inside the related model.
$res = Resourcs::with('location')->get();
$loc = $res->location[0];
$amount = $loc->pivot->amount;
Hi, sorry I forgot to mention the relationships
the resources table belongsTo the locations table. It's a one-to-many relationship. One Location has many resources, whereas a resource has only one location.
The resource table contains only the amount for a certain location_id. The resource data(like name, description, ...) is in another table "resource_data"
the resources table is built like this
id | resource_data_id | location_id | amount | created_at | updated_at
There are 7 entries for every location_id. Hence, one entry has just one resource.
But I just started to consider a many_to_many relationship:
table resources:
id | name | description
table locations:
id | name | coordinates
table location_resources
id | location_id | resource_id | amount | supply
And in the location_resources table is the current amount the already has been mined or grabbed or something. And the supply column says how much still is available for mining at this location
Is this kind of correct? :) I don't work with many-to-many relationships that often.
Like you said, in my Model I need the 'belongsToMany' method. You named the pivot table 'location_supplies'. Shouldn't it be called location_resources? Because in this pivot table you also need the current amount of mined resources in addition to the remaining supply at this location. So in my Resource model I write this:
return $this->belongsToMany('Location', 'location_supplies', 'resource_id')->withPivot('supply', 'amount');
And in the Location model it's reversed like you said.
So, if I want to have the resources at a location I could do something like this:
$location = Location::with('resources')->where('location_id', '=', 1)->get();
$resources = $location->pivot;
$amount = $resources[0]->amount;
$supply = $resources[0]->supply;
I used the Location model, so I can use the where()-method. But In the second line, when I create "$resources", what does "$location->pivot" look like? Is it an array? Because in the pivot there has to be all 7 resources, each with their amount and the remaining supply, right? Or do I misunderstand this?
I can't test it right now, I will when I get home. But in theory, is there an mistake in my thinking?
Anyways, thank you for your answer :)
Hello, sorry for the late reply. I was bussy the last few days.
I would strongly recommend you to make a Many-to-Many relationship.
Let's call the table between those models 'location_resource' (L comes before R in the alphabet).
That table doesn't need an id. It will have a composite primary key which consist of the resource_id and location_id. Theese two are also foreign keys. I would let the resource_id reference on the resource_data table, which you mentioned.
public function up()
{
Schema::table('table_name', function(Blueprint $table)
{
$table->dropColumn('id');
$table->primary(array('location_id', 'resource_id'));
}
}
public function down()
{
Schema::table('table_name', function(Blueprint $table)
{
$table->dropPrimary();
}
Schema::table('table_name', function(Blueprint $table)
{
$table->increments('id');
}
}
Your belongsToMany is almost correct. If you provide the third parameter, the name of the foreign key column name, you need to provide column names for both foreign keys.
The pivot data is an array. It is included within every referenced resource separately.
$location = Location::with('resources')->where('location_id', '=', 1)->get();
$resources = $location->resources;
$firstResource = $resources[0];
$amount = $firstResource->pivot->amount;
$supply = $firstResource->pivot->supply;
You're welcome.
Hi, thank you very much :)
I think it works now. :)
One last question: What if I only need the data from the location_resource table? Without the data from 'locations' and 'resources'. If I just need e.g. the amount and the resource_id of all of the resources at a location?
Is it okay to create a model for location_resource? So I can access it with
LocationResource::where('location_id', '=', 1)-get();
Or is there a better way?
That will surely not work, because you can't have a LocationResource model. Eloquent doesn't support models with composite primary keys. You can make something like:
DB::table('location_resource')->select('amount', 'supply')->where('...')->get();
The alternative would be to keep the id column, and create that model with id as primary.
A pivot model is mentioned in the docs, but I don't know how to make it. It seems complicated.
The Many-to-Many relationship is better in case you frequently call $location->resources. This automaticly makes the joining query for you. You can use it with and without the pivot model.
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast