MANY_MANY / HAS_MANY through / scopes on relations

Hi,

I have the following 3 tables:

user-table:

CREATE TABLE IF NOT EXISTS `user` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `username` varchar(20) NOT NULL,

  `email` varchar(100) NOT NULL,

  .........

  PRIMARY KEY (`id`),

  UNIQUE KEY `name_UNIQUE` (`username`),

  UNIQUE KEY `email_UNIQUE` (`email`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

organisation-table:

CREATE TABLE IF NOT EXISTS `organisation` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(45) NOT NULL,

  `description` text,

  `status` tinyint(4) NOT NULL,

  `create_time` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

and the pivot table, organisation-roles:

CREATE TABLE IF NOT EXISTS `organisation_roles` (

  `user_id` int(10) unsigned NOT NULL,

  `organisation_id` int(10) unsigned NOT NULL,

  `status` tinyint(4) NOT NULL,

  `leader` tinyint(1) unsigned NOT NULL,

  UNIQUE KEY `user_organisation` (`user_id`,`organisation_id`),

  KEY `fk_organisation_roles_user_id` (`user_id`),

  KEY `fk_organisation_roles_organisation_id` (`organisation_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now to my questions:

[b]I would like to view a organisation and display its leaders.

A user is a leader only if the leader flag/boolean is set to true in organisation_roles table.[/b]

The Organisation-MODEL has the following relations:

//'users' - relation is the generated one from giix

//'users' => array(self::MANY_MANY, 'User', 'organisation_roles(organisation_id, user_id)'),

'leaders' => array(self::MANY_MANY, 'User', 'organisation_roles(organisation_id, user_id)',

  'condition'=>'leaders_leaders.leader = true', // leaders_leaders is the name for organisation_roles in the generated sql

),

In the view it is currently used like this:

...

<h2><?php echo GxHtml::encode($model->getRelationLabel('leaders')); ?></h2>

<?php

	echo GxHtml::openTag('ul');

	foreach($model->leaders as $relatedModel) {

		echo GxHtml::openTag('li');

		echo GxHtml::link(GxHtml::encode(GxHtml::valueEx($relatedModel)), array('user/view', 'id' => GxActiveRecord::extractPkValue($relatedModel, true)));

		echo GxHtml::closeTag('li');

	}

	echo GxHtml::closeTag('ul');

...

I would like the somehow define a scope that i can use on this relation, that I dont need to add a new relation with condition for new roles. The new roles should then use the existing relation but by a differnt scope. Is that even the right approach for this problem (speaking of using scopes for different roles).

Should I use HAS_MANY with the through option? Does that replace MANY_MANY?

In current Yii version you can’t create new relations in finders, but you can do something like:

class Organization extends CActiveRecord {


  protected $_roles = array('leader','manager');


  public function relations() {

    $relations = array();

    $relations['users'] = array(self::MANY_MANY, 'User', 'organisation_roles(organisation_id, user_id)');

    foreach($this->_roles as $role) {

      $relationName = $role . 's';

      $relations[$relationName] = array_merge($relations['users'], array(

        'condition' => "$relationName.$role = true",

      ));

    }

    return $relations;

  }

  

  ...

}




//usage

$organization = Organization::model()->with('users','leaders','managers')->findByPk(1);

Thats a good approach, but that is using new relations for every role (of course there are not many, but I think there should not be a new relation for every role). It should somehow work with scopes, aren’t they made for that purpose?

And your code gives me the following error:

CDbException....Column not found: 1054 Unknown column 'leaders.leader' in 'where clause'.

...

FROM `user` `leaders` INNER JOIN `organisation_roles` `leaders_leaders` ON (`leaders_leaders`.`organisation_id`=:ypl0) AND (`leaders`.`id`=`leaders_leaders`.`user_id`) WHERE (leaders.leader = true). Bound with :ypl0='11' 

If you change the code to this it works:

...

foreach($this->_roles as $role) {

	$relationName = $role . 's';

	$relations[$relationName] = array_merge($relations['users'], array(

		'condition' => "{$relationName}_{$relationName}.$role = true",

	));

}

I dont know why that tables are named different in the query (speaking of for example leaders_leaders instead of leaders)