Question about many_many / belongs_many_many Arrays, Joins, Loops and Templates

Questions
1

Silverstripe Version: Silverstripe 4

Question:

I have two DataObjects, Location and Producer. I’ve established a many_many/belongs_many_many between them, like so:

Producer.php

  private static $many_many = [
    'Locations' => Location::class
  ];

Location.php

private static $belongs_many_many = [
  'Producers' => Producer::class
];

In the Locations Page Controller, I am trying to join the data, so I can print it to the template.

LocationsPageController.php

$sqlQuery = new SQLSelect();
$sqlQuery->setFrom('Location');
$sqlQuery->addLeftJoin('Producer_Locations','`Location`.`ID` = `Producer_Locations`.`LocationID`');
$sqlQuery->addLeftJoin('Producer','`Producer`.`ID` = `Producer_Locations`.`ProducerID`');
$result = $sqlQuery->execute();
$LocationList = ArrayList::create();
foreach($result as $row) {
   $LocationList->push(ArrayData::create($row));
}

I was hoping that the LocationList loop would contain a Producer Loop within each row, but all I am getting is Producer data.

Hopefully I’ve provided enough info. One thing that may be causing a problem is that each DataObject’s columns use the same column names, “Title”, “Description”, “Slug”, and “Sort”

I’ve read and reread as much documentation about many_many and belongs_many_many relationships, but I haven’t found enough info about joining and rendering the data to the template, to figure it out for myself.

Thanks for any help!

// Include any relevant code. If you have a lot of code, link to a gist instead.
2

You don’t need to get into SQL selects, the ORM will deal with that for you.

If you pass all the locations into the template, you can do all the looping in there.

In the template:

<% loop $Locations %>
  $Title <!-- this is the location title -->

  <% loop $Producers %>
    $Title <!-- This is the producer title -->
  <% end_loop %>

<% end_loop %>

Your controller would just have a method to get the locations, eg:

public function getLocations() {
  return Location::get();
}
3

@Tim, I actually tried what you suggest first, but the Producer loop is either empty, or not behaving properly. Is there a way I can check the Location object for the Producers, other than var_dump()ing?

4

Are the DataObjects versioned? If so, are they all published?

You should be able to check for valid relations if you have a look in the database. There should be a table which joins the two DataObjects and that will contain references to their IDs

5

I have the following on each data object:

private static $extensions = [
Versioned::class
];
private static $versioned_gridfield_extensions = true;

Hopefully, the Versioning is correct. All records have been published.

Additionally, there is a table in the database called ‘Producer_Locations’, which contains the reference to the IDs, as you mentioned.

6

I switched the relationship around so that the Locations has many_many Producers and the Producers belongs_many_many to Locations, like so:

Location.php

private static $many_many = [
    'Producers' => Producer::class
];

Producer.php

private static $belongs_many_many = [
    'Locations' => Location::class
];

That makes this work:

<% loop $Locations %> 
    $Title <!-- this is the location title --> 
    <% loop $Producers %> $Title 
        <!-- This is the producer title --> 
    <% end_loop %>
<% end_loop %>

I am still curious about my original question though, which has the inverse relationship between the classes. If the Location class “owns” the Producer class, is it possible to access the Location records from within a Producer loop?

 <% loop $Producers %> 
   $Title <!-- this is the location title --> 
   <% loop $Locations %> 
     <!-- Are these records accessible this way? -->
     $Title <!-- This is the producer title --> 
   <% end_loop %>
 <% end_loop %>
7

Yes, you should be able to loop over the relation from either end.