Many-to-Many relation visualisation

#1

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:

Model_Syndicate

$this->addField('name');
$this->hasMany('Syndicate_Manager');

Model_User

$this->addField('name');
$this->hasMany('Syndicate_Manager');

and finally Model_Syndicate_Manager:

$this->hasOne('User');
$this->hasOne('Syndicate');

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:

$this->addExpression('managers')->set(function($m){
    $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:

$this->addExpression('managers')->set(function($m){
    $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
#2

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
Model_SubscriptionCategoryAssociation 
    has one Lead(lead_id),
    hasOne Category (category_id), 
    last_updated_on (datetime)

expression

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

$model->addExpression('recent_'.$top_x.'_cateories')
            ->set(
                    $model->add('Model_Category',array('table_alias'=>'top'.$top_x))
                    ->addCondition('id','in',
                            $model->refSQL('SubscriptionCategoryAssociation')
                                    ->setOrder('last_updated_on')
                                    ->fieldQuery('category_id')
                        )
                    ->_dsql()
                    ->field($model->dsql()->expr("substring_index(group_concat(name SEPARATOR ', '), ', ', $top_x)"))
                )->sortable(true);

… Hope it helps some one …

:smile: