Many-to-Many relation visualisation


In this scenario I’m having many-to-many relationship between 2 of my models - Syndicate and User. I implement the relation through a 3rd model Syndicate_Manager. Here are my model definitions:





and finally Model_Syndicate_Manager:


My client wanted to see all the associated managers inside a Syndicate CRUD. The first possible solution might seem to be obvious, to add the following code inside Model_Syndicate:

    $m = $m->refSQL('Syndicate_Manager');
    return $m->fieldQuery('user');

This will work in fact, but only if you have maximum of one manager per syndicate. If you add multiple, MySQL will complain that sub-query returns multiple results. To format those results I wanted to use “group_concat()” function of MySQL. Unlike $model->count() or $model->sum() there are no method for group_concat(), so I had to build a custom expression. First I wanted to modify Syndicate_Manager class by adding a new expression around the user field. Here is the resulting code:

    $m = $m->refSQL('Syndicate_Manager','model');       // 1
    $m->addExpression('user_group')->set(function($m){  // 2
        return $m->dsql()->expr('group_concat([0] separator \', \')',  // 3 
            [$m->getElement('user')]);                  // 4
    return $m->fieldQuery('user_group');                // 5

Here is explanation for each line:

  1. I am traversing down the one-to-many reference. (see my note below about model argument)
  2. I use my Many-to-Many model do add a new expression. user_group
  3. which would be based on a custom DSQL template with one argument using a SQL statement for custom separator.
  4. Arguments are passed to expo() through array and [0] is mapped into first array argument
  5. Finally I’m returning a newly created field.

NOTE: BY default ref() and refSQL() methods will cache a related model. This is done to avoid creating multiple models when you use traversing inside foreach($model) loop. The downside, however, is if you want to modify the structure of related model a further ref() may behave differently to what you would expect.

To avoid that you can pass a second argument "model" to ref() which will always create a new model for you. If you don’t use this argument’t then subsequent $crud->addRef('Syndicate_Manager') will group your CRUD entries into one due to the presence of this field - somewhat similar to count().

Creating a nice UI for managing Many-to-Many relations

Great :slight_smile:

Extension : To get latest X no of category associations of any lead in a single column and as comma separated text
as per following structure

Model_Lead has Many SubscriptionCategoryAssociation
Model_Category has ManySubscriptionCategoryAssociation
    has one Lead(lead_id),
    hasOne Category (category_id), 
    last_updated_on (datetime)


$model = $this->add('Model_Lead');
$top_x = 3; // your number of required records

                    ->field($model->dsql()->expr("substring_index(group_concat(name SEPARATOR ', '), ', ', $top_x)"))

… Hope it helps some one …