Brain Fart Thursday - How can I implement a LEFT JOIN in the backend?

[blueprint]

I've been sat staring at my screen for a while now and I'm not making any progress.

I have two custom entities which are linked by a many-to-many relationship. I want to update a 'notStorable' field from the backend PHP through a LEFT JOIN query.

So, in my 'Part' Entity, I have a field called "Engineering Change" which is a read-only boolean. In my other Custom Entity 'Issues', I can link many parts to it (many parts can have many issues and many issues can have many parts - simples).

In my Part entity, I only want the "Engineering Change" flag to be set if any of the issues where the part is linked has a status of NOT "completed" or "abandoned".

However, I just cannot get my head around it. I have an intermediate table of "issue_part" which does the many-to-many mapping but its either too early or I need more caffeine!!

Also, I would like this "Engineering Change" to be searchable and sortable via the SelectManager so I'm unsure as to where the best place for me to do this "field update" - either in the Entity, Repository or Service PHP class.

Thanks in advance. Sorry for the rambling. Frustrated

[blueprint]

Ok, nailed it - many cups of tea later...

In the Service PHP file, I've implemented a function which does a pull from the many-to-many relationship...

// Build the parameters to search for incomplete issues
$params = [    
    'whereClause' => [
        'status!=' => [ 'Completed', 'Abandoned' ]
    ]
 ];

// Get issues related to this part
$issues = $this->getEntityManager()->getRepository('Part')->findRelated($entity, 'issues', $params);

// Count the issues
$issueCount = count($issues);

$entity->set('engineeringChange', ($issueCount > 0));