Yii Relations problem

Hi, I’m an experienced PHP dev just getting started with Yii + ORM. I’m having trouble setting up the relations in my model files.

I have 2 tables:

CREATE TABLE IF NOT EXISTS tours (

tourid int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,

unit varchar(4) NOT NULL,

address varchar(100) NOT NULL,

city varchar(50) NOT NULL,

agent varchar(50) NOT NULL,

colisted varchar(1) NOT NULL DEFAULT ‘n’,

agent2 varchar(50) DEFAULT NULL,

title varchar(400) NOT NULL,

description varchar(1000) NOT NULL,

beds int(3) NOT NULL,

baths int(3) NOT NULL,

sqft varchar(40) NOT NULL,

YouTube varchar(500) NOT NULL,

Status varchar(1) NOT NULL DEFAULT ‘a’,

price varchar(15) NOT NULL,

mls varchar(15) NOT NULL,

lot varchar(40) NOT NULL,

maint float NOT NULL,

processed varchar(44) NOT NULL,

created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

numpics tinyint(3) NOT NULL,

MlsPhotoStatus enum(‘sent’) DEFAULT NULL,

notified enum(‘yes’,‘no’) NOT NULL DEFAULT ‘no’,

PRIMARY KEY (tourid),

KEY agent (agent),

KEY agent_2 (agent)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3058 ;

CREATE TABLE IF NOT EXISTS agent (

id int(4) NOT NULL AUTO_INCREMENT,

username varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

password char(41) NOT NULL,

name varchar(100) NOT NULL,

agency varchar(100) NOT NULL,

phone varchar(50) NOT NULL,

fax varchar(50) NOT NULL,

email varchar(50) NOT NULL,

url varchar(200) NOT NULL,

mug varchar(1) NOT NULL DEFAULT ‘n’,

tagline varchar(500) DEFAULT NULL,

bgColour varchar(7) DEFAULT NULL,

textColour varchar(7) DEFAULT NULL,

analytics varchar(500) DEFAULT NULL,

billing enum(‘agency’,‘name’) NOT NULL,

shipping enum(‘agency’,‘home’,‘pickup’,‘listing’) NOT NULL,

joined timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

facebook varchar(85) NOT NULL,

gplus varchar(45) NOT NULL,

linkedin varchar(85) NOT NULL,

twitter varchar(45) NOT NULL,

header varchar(10) NOT NULL,

sendBoard enum(‘yes’,‘no’) NOT NULL DEFAULT ‘yes’,

social enum(‘yes’,‘no’) NOT NULL DEFAULT ‘no’,

PRIMARY KEY (name),

UNIQUE KEY id (id),

KEY name (name)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=325 ;

tours.agent and tours.agent2 are both linked to the agent table referencing agent.name

When I run this command (trying to add a fk relationship)

ALTER TABLE agent ADD FOREIGN KEY ( name ) REFERENCES vrtours.tours (

agent

);

I get this error:

Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

I’ve googled this and tried to troubleshoot for hours and I can’t figure it out. I thought maybe because there were entries in tours.agent that are not in agent.name so I deleted all tours except 2 (both of which their tour.agent were in agent.name) and it still did not work…

The reason I’m trying to get the table structure right is so Yii can auto generate the relations. I tried setting up the relation manually using: in my tours model:

‘agent0’ => array(self::BELONGS_TO, ‘Tours’, ‘name’),

‘tours’ => array(self::HAS_MANY, ‘Tours’, ‘agent’),

When I do this(tours view):

$agentInfo = $model->agent0;

I just get a tour object?

Also, In my agent model I have this as the relation:

‘tours’ => array(self::HAS_MANY, ‘Tours’, ‘agent’),

when I do this (agent view):

$a=$model->tours;

I get:

Property "Agent.agent" is not defined.

I’m not sure where else to try, could it be because yii is getting confused since there are technically 2 fields in the tours table (agent and agent2) both linking to agent.name table?

I would greatly appreciate some help :(

You already have data in the table, so it fails. You need to add data to the fk field in your existing dataset before you put a constraint on it.

Also your schema is pretty bad.

First: You have a field named agent2, but reference it at agent_2 in your constraint.

Second: You have ID fields, but use a varchar field as primary key. Why??

Third: analytics varchar(500) ? varchar limited to 255

Fourth: notified enum(‘yes’,‘no’) ? Why not tinyint(1)?

Having two agent fields in the same table suggests crappy schema. I suggest a M:M relationship with a join table.

Hi thanks for the reply.

I know the schema is terrible: it’s something I inherited and don’t have the power to change.

Anyway I got it working by using these relations:

In Tours model:

public function relations()

{


	// NOTE: you may need to adjust the relation name and the related


	// class name for the relations automatically generated below.


	return array(


		'agents' => array(self::HAS_MANY, 'Agent', 'name'),


		'agent0' => array(self::BELONGS_TO, 'Agent', 'agent'),


		'tours' => array(self::HAS_MANY, 'Tours', 'agent'),


	);


}

In Agent model:

public function relations()

{


	// NOTE: you may need to adjust the relation name and the related


	// class name for the relations automatically generated below.


	return array(


		'name0' => array(self::BELONGS_TO, 'Tours', 'name'),


		'tours' => array(self::HAS_MANY, 'Tours', 'agent'),


	);


}

Thanks for your help :)