parameterise relation in CActiveRecord

Hi there,

How can I parameterise relations function in CActiveRecord? Here’s the situation.

group Table (Database)

id

name

contact Table (Database)

id

name

mobile_no

group-contact Table (Database): links the two tables together.

group_id

contact_id

How is it possible to get contacts under a group by CActiveDataProvider? I’ve tried:

In ContactTbl CActiveRecord (contact table model):

public function relations() {

  return array(

    'group' => array(self::MANY_MANY, 'GroupTbl', 'group-contact(group_id, contact_id)',

       'select' => 'group.id'

    ),

  );

}


public function listingByGroup($group, $page = 0) {

  $criteria = new CDbCriteria();

  $criteria->select = array('t.id', 't.name', 't.mobile_no');

  $criteria->condition = 't.deleted IS NULL AND group.id = :group';

  $criteria->params = array(':group' => $group);

  $criteria->order = 't.name, t.mobile_no';

  $criteria->with = 'group';

		

  return new CActiveDataProvider('ContactTbl', array(

    'criteria' => $criteria,

    'pagination' => array(

      'pageSize' => 1,

      'currentPage' => $page,

    ),

  ));

}

Error:

[color="#FF0000"]

2010/05/06 07:43:13 [error] [system.db.CDbCommand] Error in querying SQL: SELECT t.id AS t0_c0, t.name AS t0_c1, t.mobile_no AS t0_c2 FROM contact t WHERE (t.deleted IS NULL AND group.id = :group) ORDER BY t.name, t.mobile_no LIMIT 1. Bind with parameter :group=1

2010/05/06 07:43:13 [error] [exception.CDbException] exception ‘CDbException’ with message ‘CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘group.id’ in ‘where clause’’ in \apache_server\yii1.1\framework\db\CDbCommand.php:375

[/color]

Try:

public function relations() {

  return array(

    'group' => array(self::MANY_MANY, 'GroupTbl', 'group-contact(group_id, contact_id)',

       'select' => 'group.id'

       'condition' => 'group.id=:group'

    ),

  );

}

$criteria->with = array('group'=>array('params'=>array(':group',$group)));

Hi,

I have been tested the suggested code you gave. According to my log, the codes does work but the return records aren’t correct. I have attached the query log below.

[color="#FF0000"]

[system.db.CDbCommand] Querying SQL: SELECT COUNT(DISTINCT t.id) FROM contact t LEFT OUTER JOIN group-contact group_group ON (t.id=group_group.contact_id) LEFT OUTER JOIN group group ON (group.id=group_group.group_id) WHERE (t.deleted IS NULL) AND (group.id = :group). Bind with parameter :group=1

[system.db.ar.CActiveRecord] ContactTbl.findAll()

[system.db.CDbCommand] Querying SQL: SELECT t.id AS t0_c0, t.firstname AS t0_c1, t.lastname AS t0_c2, t.mobile_no AS t0_c4, t.email AS t0_c5 FROM contact t WHERE (t.deleted IS NULL) ORDER BY t.firstname, t.lastname, t.mobile_no, t.email LIMIT 1 OFFSET 1

[system.db.CDbCommand] Querying SQL: SELECT t.id AS t0_c0, group.id AS t1_c0 FROM contact t LEFT OUTER JOIN group-contact group_group ON (t.id=group_group.contact_id) LEFT OUTER JOIN group group ON (group.id=group_group.group_id) WHERE (t.id=‘8’) AND (group.id = :group). Bind with parameter :group=1

[/color]

I realised that CActivedDataProvider somehow query a single record which is not required (3rd query).