ManyToMany: Loading all non-referenced recods


Hi there,

I want to share this so its not lost.
In my Data Model I have Tours which can have many Groups, and the Groups can have many Tours, so its a MtoM relation. The mapping table Object is called GroupToTour and has the fields ‘tour_id’ and ‘group_id’.

Loading all referenced records is easy:

$tour->ref('GroupToTour')->ref('group_id') loads all Groups of this Tour

But what about doing the contrary, getting all Groups that are NOT referenced to this tour? Of course with a single DB request.
The solution is:

$groups = new \Pmg\Group($db);
$x = (new \Pmg\GroupToTour(db))->addCondition('tour_id', $this->get('id'));
$groups->addCondition('id', 'not in', $x->action('field', ['group_id']));

Hope this helps some, credits go to Imants