Yii Framework Forum: parameterise relation in CActiveRecord - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

parameterise relation in CActiveRecord Rate Topic: -----

#1 User is offline   resplendent 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 22-May 09
  • Location:Singapore

Posted 06 May 2010 - 02:52 AM

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:

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

0

#2 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 06 May 2010 - 06:44 AM

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)));

0

#3 User is offline   resplendent 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 22-May 09
  • Location:Singapore

Posted 10 May 2010 - 11:16 AM

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.


[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


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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users