ramin2nt2
December 5, 2010, 8:53am
1
hi
i have 3 tables
student,
class,
classification
i want to join these tables
i wrote a sql code:
SELECT student . * , class.name
FROM classification
INNER JOIN student
USING ( studentid )
INNER JOIN class
USING ( classid )
how can i define the relations in my model ?
i define these relations, but not works correctly
class:
public function relations(){
return array(
'EStudent' => array(self::MANY_MANY, 'EStudent', 'classification(classId, studentId)', 'joinType' => 'INNER JOIN'),
);
}
student:
public function relations(){
return array(
'EClass' => array(self::MANY_MANY, 'EClass', 'classification(studentId,classId)', 'joinType' => 'INNER JOIN'),
);
}
classification:
public function relations(){
return array(
'EStudent' => array(self::HAS_MANY, 'EStudent', 'studentId', 'joinType' => 'INNER JOIN'),
'EClass' => array(self::HAS_MANY, 'EClass', 'classId', 'joinType' => 'INNER JOIN'),
);
}
controller:
$dataProvider = new CActiveDataProvider('EClassification');
$dataProvider->criteria->with = array('EStudent', 'EClass');
please help
ramin2nt2
December 5, 2010, 8:56am
2
it’s my tables schema
CREATE TABLE IF NOT EXISTS `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`studentId` int(11) NOT NULL,
`firstName` tinytext COLLATE utf8_persian_ci NOT NULL,
`lastName` tinytext COLLATE utf8_persian_ci NOT NULL,
`gender` int(11) NOT NULL,
`idNumber` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`nationalCode` varchar(10) COLLATE utf8_persian_ci NOT NULL,
`personalCode` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`issued` tinytext COLLATE utf8_persian_ci NOT NULL,
`nationality` tinytext COLLATE utf8_persian_ci NOT NULL,
`religion` int(11) NOT NULL,
`familyStatus` int(11) NOT NULL,
`password` tinytext COLLATE utf8_persian_ci NOT NULL,
`grade` int(11) NOT NULL,
`mobile` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`phone` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`email` varchar(50) COLLATE utf8_persian_ci NOT NULL,
`address` text COLLATE utf8_persian_ci NOT NULL,
`fatherName` tinytext COLLATE utf8_persian_ci NOT NULL,
`fatherMobile` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`fatherJob` tinytext COLLATE utf8_persian_ci NOT NULL,
`motherName` tinytext COLLATE utf8_persian_ci NOT NULL,
`motherMobile` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`motherJob` tinytext COLLATE utf8_persian_ci NOT NULL,
`image` tinytext COLLATE utf8_persian_ci NOT NULL,
`active` int(11) NOT NULL,
`type` int(11) NOT NULL,
`dateRegister` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CHECKSUM=1 AUTO_INCREMENT=279 ;
CREATE TABLE IF NOT EXISTS `class` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`classId` int(11) NOT NULL,
`gradeId` int(11) NOT NULL,
`name` tinytext COLLATE utf8_persian_ci NOT NULL,
`capacity` int(11) NOT NULL,
`active` int(11) NOT NULL,
`description` text COLLATE utf8_persian_ci NOT NULL,
`createTime` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `classId` (`classId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=11 ;
CREATE TABLE IF NOT EXISTS `classification` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`classId` int(11) NOT NULL,
`studentId` int(11) NOT NULL,
`active` int(11) NOT NULL,
`createTime` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=277 ;
galymzhan
December 5, 2010, 10:46am
3
classification is an associations table, so you don’t need ActiveRecord class for it. You’ve correctly defined relations for class and student classes. Try this:
$dataProvider = new CActiveDataProvider('EStudent', array(
'criteria' => array(
'select' => 't.*, EClass.name',
'with' => array('EClass'),
)
);
Not sure, but I think you must set correct foreign keys in classification table.
ramin2nt2
December 5, 2010, 1:08pm
4
should i use MANY_MANY or HAS_MANY relation ?
galymzhan
December 5, 2010, 3:22pm
5
Student has many Classes and Class has many Students, right?
Therefore, you should use MANY_MANY relation.
