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