2

I have a resources table set up in the following way:

resources
  id - integer, pk
  name - string
  description - string
  type_id - integer, fk
  ...

My goal is to set up a many-to-many relationship between the items in the same table. So when a user is viewing one resource they will also be able to see all the other resources that are linked to it.

I'm assuming I will need to set up a pivot table, something like ...

resource_links
  id - integer, pk
  id_left- integer
  id_right - integer

Then I can set up a query scope to select entries from the pivot table where id_left or id_right is equal to the resource's id, and return all rows from the resources table where id_left/right is not equal to the current resource's id.

What is the best way to do what I want?

UPDATE:
My resources table also has a column to define what type of resource it is. Is it possible, using the belongsToMany relation, to retrieve only those linked resources that are of a specific type?

chipit24
  • 6,509
  • 7
  • 47
  • 67

1 Answers1

4

What about

resources
  id - integer
  name - string
  description - string

resource_links
  id - integer
  resource_id - integer
  linked_resource_id - integer

This makes it a bit clearer to anyone looking at the pivot what it is doing. Then you can retrieve the linked resources for a given resource ($resource_id below) with something like 

$linked_resources = DB::table('resources')
                ->join('resource_links', 'resources.id', '=', 'resource_links.linked_resource_id')
                ->where('resource_links.resource_id', '=', $resource_id)
                ->get();

Edit:

Or you could add a many to many relationship on the model back to itself

public function linkedResources()
{
    return $this->belongsToMany('Resource', 'resource_links', 'resource_id', 'linked_resource_id');
}

And then return the linked resources with 

Resource::find(1)->linked_resources;

Edit:

If you wanted to limit the linked resources by type something like the following is one approach

$linked_resources = Resource::with(array('linkedResources' => function($query) {
        $query->where('type', '=', 'book');
    }))->where('id','=',1)->get();
glendaviesnz
  • 1,889
  • 14
  • 12
  • Although a many-to-many relation (with a pivot table) may not work, a many-to-many polymorphic relation should work (allowing you to access the relation through the model by just defining a morphedByMany function). This is how I have my comments set up - allowing me to associate comments with resources as well as other comments (for the case of replies to comments) without having to set up two pivot tables. – chipit24 Jun 19 '14 at 16:53
  • actually a straight many to many on the model does work - i just made the mistake of using snake_case on the relationship name instead of camel case - have edited above - but as you note morphedby many would be another option – glendaviesnz Jun 19 '14 at 20:28
  • Ah, ok! I'll give that a try! – chipit24 Jun 19 '14 at 20:36
  • My resources table also has a column to define what type of resource it is. Is it possible, using the `belongsToMany` relation, to retrieve only those linked resources that are of a specific type? – chipit24 Jun 19 '14 at 22:17
  • For anyone reading this in the future, I ended up setting up a many to many relationship on the model back to itself. I then ran into the issue of the relation being 'one way' (see: http://stackoverflow.com/questions/17567305/laravel-many-to-many-self-referencing-table-only-works-one-way) which I solved by just adding two entries in the pivot table for each relationship. – chipit24 Jul 21 '14 at 15:46