Relational Active Record Question

Relational Active Record Question Trying MANY_MANY relationship Rate Topic:

#1 PauK

Junior Member

Group: Members
Posts: 32
Joined: 21-November 09
Location:region krasnodarskiy

Posted 06 December 2009 - 04:47 AM

Hello, EveryBody

I have Db connection

'db'=>array(
		'connectionString'=>'mysql:host=localhost;dbname=siteconst_yiicore',
		'tablePrefix' => 'siteconst_',

		//'class'=>'CDbConnection',
		'username'=>'root',
		'password'=>'',
		'emulatePrepare'=>true, // needed by some MySQL installations
		'charset'=>'utf8',
		),

I Have db structure for Mysql 5.1

CREATE TABLE siteconst_Structure (
  id int(11) NOT NULL auto_increment,
  title varchar(255) NOT NULL,
...
  PRIMARY KEY  (id),
...
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE siteconst_Widget (
  id int(11) NOT NULL auto_increment,
  title varchar(255) NOT NULL,
...
  PRIMARY KEY  (id),
 ...
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE siteconst_WidgetStructure (
  object_id int(11) NOT NULL,
  structure_id int(11) NOT NULL,
...
  PRIMARY KEY  (object_id,structure_id),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `siteconst_WidgetStructure`
  ADD CONSTRAINT siteconst_WidgetStructure_ibfk_1 FOREIGN KEY (object_id) REFERENCES siteconst_Widget (id),
  ADD CONSTRAINT siteconst_WidgetStructure_ibfk_2 FOREIGN KEY (structure_id) REFERENCES siteconst_Structure (id);

I have apropriate models

<?php

class Structure extends CActiveRecord
{
	public function tableName()
	{
		return '{{Structure}}';
	}
...
	public function relations()
	{
	
	return array(
	'widgets'=>array(self::MANY_MANY,'WidgetModel','{{WidgetStructure}}(object_id,structure_id)')
	);
	}	
}



class WidgetModel extends CActiveRecord
{
	public function tableName()
	{
		return '{{Widget}}';
	}

	public function relations()
	{
	return array(
	'structures'=>array(self::MANY_MANY,'WidgetStructure','{{WidgetStructure}}(structure_id object_id)')
	);
	}

}
class WidgetStructure extends CActiveRecord
{
	public function tableName()
	{
		return '{{WidgetStructure}}';
	}

	public function relations()
	{
	
	}
}
?>

Now I m trying get Widgets for appropriate structure

$model=Structure::model()->findbyPk($structure_id);
$model->widgets;

And i get error

CDbException
Description

The relation "widgets" in active record class "Structure" is specified with an invalid foreign key "object_id". The foreign key does not point to either joining table.

What iv done wrong?
Thank you

#2 jayrulez

Advanced Member

Group: Members
Posts: 348
Joined: 29-July 09

Posted 06 December 2009 - 11:45 AM

PaulKh, on 06 December 2009 - 04:47 AM, said:

Hello, EveryBody

I have Db connection

'db'=>array(
		'connectionString'=>'mysql:host=localhost;dbname=siteconst_yiicore',
		'tablePrefix' => 'siteconst_',

		//'class'=>'CDbConnection',
		'username'=>'root',
		'password'=>'',
		'emulatePrepare'=>true, // needed by some MySQL installations
		'charset'=>'utf8',
		),

I Have db structure for Mysql 5.1

CREATE TABLE siteconst_Structure (
  id int(11) NOT NULL auto_increment,
  title varchar(255) NOT NULL,
...
  PRIMARY KEY  (id),
...
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE siteconst_Widget (
  id int(11) NOT NULL auto_increment,
  title varchar(255) NOT NULL,
...
  PRIMARY KEY  (id),
 ...
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE siteconst_WidgetStructure (
  object_id int(11) NOT NULL,
  structure_id int(11) NOT NULL,
...
  PRIMARY KEY  (object_id,structure_id),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `siteconst_WidgetStructure`
  ADD CONSTRAINT siteconst_WidgetStructure_ibfk_1 FOREIGN KEY (object_id) REFERENCES siteconst_Widget (id),
  ADD CONSTRAINT siteconst_WidgetStructure_ibfk_2 FOREIGN KEY (structure_id) REFERENCES siteconst_Structure (id);

I have apropriate models

<?php

class Structure extends CActiveRecord
{
	public function tableName()
	{
		return '{{Structure}}';
	}
...
	public function relations()
	{
	
	return array(
	'widgets'=>array(self::MANY_MANY,'WidgetModel','{{WidgetStructure}}(object_id,structure_id)')
	);
	}	
}



class WidgetModel extends CActiveRecord
{
	public function tableName()
	{
		return '{{Widget}}';
	}

	public function relations()
	{
	return array(
	'structures'=>array(self::MANY_MANY,'WidgetStructure','{{WidgetStructure}}(structure_id object_id)')
	);
	}

}
class WidgetStructure extends CActiveRecord
{
	public function tableName()
	{
		return '{{WidgetStructure}}';
	}

	public function relations()
	{
	
	}
}
?>

Now I m trying get Widgets for appropriate structure

$model=Structure::model()->findbyPk($structure_id);
$model->widgets;

And i get error

CDbException
Description

The relation "widgets" in active record class "Structure" is specified with an invalid foreign key "object_id". The foreign key does not point to either joining table.

What iv done wrong?
Thank you

I think i see a few things wrong, but y not just have yiic shell generate ur relations for you?

php:
foreach(array('cat', 'dog', 'cow') as $animal) echo $animal."\n";

python:
[(animal, print(animal)) for animal in ['cat', 'dog', 'cow']]

ruby:
['cat', 'dog', 'cow'].each {|animal| puts animal}

You say Tomato, I say Tomato.

#3 PauK

Junior Member

Group: Members
Posts: 32
Joined: 21-November 09
Location:region krasnodarskiy

Posted 06 December 2009 - 11:50 PM

jayrulez, on 06 December 2009 - 11:45 AM, said:

I think i see a few things wrong, but y not just have yiic shell generate ur relations for you?

What thinks wrong
Yes i made yiic CRUD and made some tune after this

#4 PauK

Junior Member

Group: Members
Posts: 32
Joined: 21-November 09
Location:region krasnodarskiy

Posted 10 December 2009 - 10:44 AM

I found bug in YII 1.1b
AR tableName() should return only full name
I create behavior TableNameHack
and use it in my models:

    public function tableName()
	{
		return $this->prefixedTableName('{{Widget}}');
	}

   	 public function behaviors(){
  	      return array(
   	         'TableNameHack' => array(
                'class' => 'application.extensions.TableNameHack'
    	        )
  	      );
 	}

class TableNameHack extends CActiveRecordBehavior {

   
    
   public function prefixedTableName($tablename){
	return preg_replace('/{{(.*?)}}/',YII::app()->db->tablePrefix.'\1',$tablename);
   }

}