XF 2.0 TO_MANY relation through many-to-many table

[CMTV]

Hi!

The question is simple. I hope it is.

I have three tables: criteria, params and criteria_params. The last one consists of two columns with criterion and param IDs. So it is a many-to-many relation between criteria and params. One criteria can have many params. One param can belong to any criteria.

For example. My param with param_id equals 1 belongs to 3 criteria with following ids: 7, 8, 22.

This data is stored only in criteria_params table:
1 | 7
1 | 8
1 | 22

From specific param entity, how can I get all criteria it belongs to?

// Param entity class

$structure->relations = [
    'Criteria' => [
        'entity' => 'PHPCriteria:Criterion',
        'type' => self::TO_MANY,
        'conditions' => ?????
    ]
];

What should I write on '?????' place since I don't store connected criteria ids in params table? They are in the criteria_params table.

 

[Jeremy P]

Unfortunately the ORM doesn't natively support pivot tables, but you can emulate them pretty easily. Start by creating an entity for your pivot table (ie CriteriaParams) with relations for both Criteria and Params. Then in your Criteria and Params entities, set up a to-many relation to the pivot table entity (using criteria_id or param_id as conditions), and use a getter to actually perform the pivot:

/**
* @return \XF\Mvc\Entity\AbstractCollection
*/
public function getCriteria()
{
    $pivot = $this->Criteria_; // or whatever you named your pivot relation
    return $pivot->pluckNamed('Criteria', 'criteria_id');
}

Edit: Don't forget to add 'with' => 'Criteria' (or Params) to your pivot relations.

 

[CMTV]

Thank you @Jeremy P I will try this right now this and tell the results.

 

[CMTV]

Works like a charm! Thank you again!

One thing to note: relations in CriteriaParams entity should be of type TO_ONE.