ORM many_many relationship

Questions
1

Silverstripe Version: 5.1

If I have the following DataObjects and relations:

class ProjectSector extends DataObject
{
    private static $many_many = [  
        'Examples' => Example::class,
        'OtherExamples' => OtherExample::class,
    ];
}

class Example extends DataObject
{
    private static $has_one = [
        'ProjectPost' => ProjectPost::class,
    ];

    private static $belongs_many_many = [
        'ProjectSectors' => ProjectSector::class,
    ];
}

class OtherExample extends DataObject
{
    private static $has_one = [
        'ProjectPost' => ProjectPost::class,
    ];

    private static $belongs_many_many = [
        'ProjectSectors' => ProjectSector::class,
    ];
}

class ProjectPost extends BlogPost
{
    private static $has_many = [
        'Examples' => Example::class,
        'OtherExamples' => OtherExample::class,
    ];

    //...

    public function ProjectSectors()
    {
        $arrayList = ArrayList::create();
    
        foreach (self::$has_many as $examplesRelationName) {

            foreach ($this->{$examplesRelationName}() as $example) {

                foreach ($example->ProjectSectors() as $dataObject) {

                    $arrayList->push($dataObject);

                }

            }

        }

        return $arrayList->removeDuplicates();
    }
}

Question:
Is there an ORM method to list all ProjectSector records associated with a ProjectPost by proxy, i.e. through its Example & OtherExample relations?

I am currently using the ProjectSectors method on ProjectPost, but it ain’t pretty and I feel like I am missing something obvious here :thinking:

Any pointers gratefully received! :pray:

2

Something like this should work - Gets all ProjectSector records where it has any Examples or OtherExamples relation which in turn has a ProjectPost relation to the current record.

ProjectSector::get()->filterAny([
    'Examples.ProjectPost.ID' => $this->ID,
    'OtherExamples.ProjectPost.ID' => $this->ID,
]);
3

Thank you @GuySartorelli!

Now, what if I wanted to come at it from the other end and list all ProjectPosts associated with a ProjectSector via its Examples & OtherExamples?

I did wonder if this may be what many_many through is for, but I am not so sure after reading the docs?

4

Now, what if I wanted to come at it from the other end and list all ProjectPosts associated with a ProjectSector via its Examples & OtherExamples?

That should just be the same query, but the other way around, where $this represents a ProjectSector record:

ProjectPost::get()->filterAny([
    'Examples.ProjectSectors.ID' => $this->ID,
    'OtherExamples.ProjectSectors.ID' => $this->ID,
]);

I did wonder if this may be what many_many through is for, but I am not so sure after reading the docs?

If your Example and OtherExample records are only ever used as intermediaries between posts and sectors, you could use many_many through for that.

5

Thanks @GuySartorelli, you’re a legend :clap: `