This will return an array of 3 objects category, each of wich has a property Items.
In this property you will get 3 objects items in the first category, 2 in the second and 0 in the third.
This is the Yii approach to the database. If you want do to something different, you can check the documentation of CDbCriteria (the object that will handle the array you pass to find) and see what it can do.
I’ve check the database log, the generated sql are all using “LEFT OUTER JOIN”, isn’t it already using left join??
I got the left outer join by setting the "together = true", if i use your second approach, because the records of item is very very huge, it will be very slow if faching all the data without any filtering.
Because the real code is very complex, but I show here, please check
/**
* @return array relational rules.
*/
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(
'month' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 1, 2) = month.code
and month.cat = \'MONTH\'
',),
'course' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 3, 1) = course.code
and course.cat = \'COURSE\'
',),
'subject' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 4, 3) = subject.code
and subject.cat = \'SUBJECT\'
',),
'level' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 7, 2) = level.code
and level.cat = \'LEVEL\'
',),
'tutor' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 9, 3) = tutor.code
and tutor.cat = \'TUTOR\'
',),
'class' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 12, 1) = class.code
and class.cat = \'CLASS\'
',),
'mode' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 13, 1) = mode.code
and mode.cat = \'MODE\'
',),
'center' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 14, 1) = center.code
and center.cat = \'CENTER\'
',),
'language' => array(self::BELONGS_TO, 'CourseCode', '', 'on' => '
substring(t.course_code, 15, 2) = language.code
and language.cat = \'LANGUAGE\'
',),
'lessons' => array(self::HAS_MANY, 'Lesson', 'sys_course_id'),
);
}
/**
* Retrieves a list of models based on the current search/filter conditions.
* @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
*/
public function searchCourse() {
$sort = new CSort();
$sort->defaultOrder = 't.course_code';
$criteria = new CDbCriteria;
//$criteria->compare('month.code', 12);
$criteria->compare('t.academic_year', $this->academic_year);
//$criteria->compare('t.academic_year', 2005);
$criteria->with = array(
'month',
'course',
'subject',
'level',
'tutor',
'class',
'mode',
'center',
'language',
'lessons',
);
$criteria->together = true;
return new CActiveDataProvider(get_class($this), array(
'criteria' => $criteria,
'sort' => $sort,
'pagination' => false,
'pagination' => array('pageSize' => 10),
));
}
As you see, I’ve set the pageSize to 10
But the actually records I got is sometimes less than 10
(e.g. One course contains 4 lessons
In this case, I’ll got 3 records, But i want to get 10 courses plus they lessons (10x4)
You can add condition and paging for reduce the number of result, you can set Select for select only some fields.
You can do the query you want, the difference is only that data will be incapslutated in models, and you can access this data using the properties of the models.
I don’t understand very well what exactly you have to do, if you post a scratch of sql you want to generate, we can explain you how to achive with AR.
ok, i got the following generated, the sql is fine, perfect, but the result from Yii is not like that
SELECT "t"."sys_course_id" AS "t0_c0",
"t"."course_code" AS "t0_c1", "t"."academic_year" AS "t0_c2",
"t"."open_date" AS "t0_c3", "t"."time_from" AS "t0_c4", "t"."time_to" AS
"t0_c5", "t"."enrolment_date_1" AS "t0_c6", "t"."enrolment_date_2" AS
"t0_c7", "t"."total_phases" AS "t0_c8", "t"."sys_room_id" AS "t0_c9",
"t"."init_capacity" AS "t0_c10", "t"."fee_1" AS "t0_c11", "t"."fee_2" AS
"t0_c12", "t"."fee_3" AS "t0_c13", "t"."discount_1" AS "t0_c14",
"t"."discount_2" AS "t0_c15", "t"."discount_3" AS "t0_c16",
"t"."total_lesson" AS "t0_c17", "t"."have_share" AS "t0_c18",
"t"."share_course_id" AS "t0_c19", "t"."description" AS "t0_c20",
"t"."center_comments" AS "t0_c21", "t"."remarks" AS "t0_c22", "t"."status"
AS "t0_c23", "t"."sys_rec_locked" AS "t0_c24", "t"."sys_dorc" AS "t0_c25",
"t"."sys_dorm" AS "t0_c26", "t"."sys_rcb" AS "t0_c27", "t"."sys_rmb" AS
"t0_c28", "t"."sys_status" AS "t0_c29", "t"."sys_logtime" AS "t0_c30",
"t"."sys_logserver" AS "t0_c31", "t"."noe" AS "t0_c32", "t"."noe_share" AS
"t0_c33", "t"."tt_quota" AS "t0_c34", "t"."old_parent_course_id" AS
"t0_c35", "t"."parent_course_id" AS "t0_c36", "month"."cat" AS "t1_c0",
"month"."code" AS "t1_c1", "month"."sub_cat" AS "t1_c2", "month"."contents"
AS "t1_c3", "month"."sys_status" AS "t1_c4", "month"."allow_online" AS
"t1_c5", "course"."cat" AS "t2_c0", "course"."code" AS "t2_c1",
"course"."sub_cat" AS "t2_c2", "course"."contents" AS "t2_c3",
"course"."sys_status" AS "t2_c4", "course"."allow_online" AS "t2_c5",
"subject"."cat" AS "t3_c0", "subject"."code" AS "t3_c1",
"subject"."sub_cat" AS "t3_c2", "subject"."contents" AS "t3_c3",
"subject"."sys_status" AS "t3_c4", "subject"."allow_online" AS "t3_c5",
"level"."cat" AS "t4_c0", "level"."code" AS "t4_c1", "level"."sub_cat" AS
"t4_c2", "level"."contents" AS "t4_c3", "level"."sys_status" AS "t4_c4",
"level"."allow_online" AS "t4_c5", "tutor"."cat" AS "t5_c0", "tutor"."code"
AS "t5_c1", "tutor"."sub_cat" AS "t5_c2", "tutor"."contents" AS "t5_c3",
"tutor"."sys_status" AS "t5_c4", "tutor"."allow_online" AS "t5_c5",
"class"."cat" AS "t6_c0", "class"."code" AS "t6_c1", "class"."sub_cat" AS
"t6_c2", "class"."contents" AS "t6_c3", "class"."sys_status" AS "t6_c4",
"class"."allow_online" AS "t6_c5", "mode"."cat" AS "t7_c0", "mode"."code"
AS "t7_c1", "mode"."sub_cat" AS "t7_c2", "mode"."contents" AS "t7_c3",
"mode"."sys_status" AS "t7_c4", "mode"."allow_online" AS "t7_c5",
"center"."cat" AS "t8_c0", "center"."code" AS "t8_c1", "center"."sub_cat"
AS "t8_c2", "center"."contents" AS "t8_c3", "center"."sys_status" AS
"t8_c4", "center"."allow_online" AS "t8_c5", "language"."cat" AS "t9_c0",
"language"."code" AS "t9_c1", "language"."sub_cat" AS "t9_c2",
"language"."contents" AS "t9_c3", "language"."sys_status" AS "t9_c4",
"language"."allow_online" AS "t9_c5", "lessons"."sys_lesson_id" AS
"t10_c0", "lessons"."sys_course_id" AS "t10_c1", "lessons"."the_date" AS
"t10_c2", "lessons"."time_from" AS "t10_c3", "lessons"."time_to" AS
"t10_c4", "lessons"."lesson_num" AS "t10_c5", "lessons"."lesson_type" AS
"t10_c6", "lessons"."remuneration_rate" AS "t10_c7",
"lessons"."lesson_mis_charge" AS "t10_c8", "lessons"."fee_1" AS "t10_c9",
"lessons"."fee_2" AS "t10_c10", "lessons"."fee_3" AS "t10_c11",
"lessons"."discount_1" AS "t10_c12", "lessons"."discount_2" AS "t10_c13",
"lessons"."discount_3" AS "t10_c14", "lessons"."status" AS "t10_c15",
"lessons"."sys_rec_locked" AS "t10_c16", "lessons"."sys_dorc" AS "t10_c17",
"lessons"."sys_dorm" AS "t10_c18", "lessons"."sys_rcb" AS "t10_c19",
"lessons"."sys_rmb" AS "t10_c20", "lessons"."sys_status" AS "t10_c21",
"lessons"."sys_logtime" AS "t10_c22", "lessons"."sys_logserver" AS
"t10_c23", "lessons"."noe" AS "t10_c24", "lessons"."tt_quota" AS "t10_c25",
"lessons"."tt_noe" AS "t10_c26", "lessons"."remuneration_rate2" AS
"t10_c27" FROM "t_course" "t" LEFT OUTER JOIN "t_course_code" "month" ON (
substring(t.course_code, 1, 2) = month.code
and month.cat = 'MONTH'
) LEFT OUTER JOIN "t_course_code" "course" ON (
substring(t.course_code, 3, 1) = course.code
and course.cat = 'COURSE'
) LEFT OUTER JOIN "t_course_code" "subject" ON (
substring(t.course_code, 4, 3) = subject.code
and subject.cat = 'SUBJECT'
) LEFT OUTER JOIN "t_course_code" "level" ON (
substring(t.course_code, 7, 2) = level.code
and level.cat = 'LEVEL'
) LEFT OUTER JOIN "t_course_code" "tutor" ON (
substring(t.course_code, 9, 3) = tutor.code
and tutor.cat = 'TUTOR'
) LEFT OUTER JOIN "t_course_code" "class" ON (
substring(t.course_code, 12, 1) = class.code
and class.cat = 'CLASS'
) LEFT OUTER JOIN "t_course_code" "mode" ON (
substring(t.course_code, 13, 1) = mode.code
and mode.cat = 'MODE'
) LEFT OUTER JOIN "t_course_code" "center" ON (
substring(t.course_code, 14, 1) = center.code
and center.cat = 'CENTER'
) LEFT OUTER JOIN "t_course_code" "language" ON (
substring(t.course_code, 15, 2) = language.code
and language.cat = 'LANGUAGE'
) LEFT OUTER JOIN "t_lesson" "lessons" ON
("lessons"."sys_course_id"="t"."sys_course_id") WHERE
(t.academic_year=:ycp0) ORDER BY t.course_code LIMIT 10
There is a LIMIT 10, I ran that sql by postgres admin, I got 10 rows (joined results)
But what I got from Yii is something like "distincted" results, by the parent table (course)
I agree with your second approach, by using the property to retrieve the lessons
But the problem is that, the model gave me only 3 courses, not 10 courses, I actually want 10 courses
How many entries you have in courses? If 3, Yii will give you only 3 entries in model, there is nothing to do about this.
If you have 10 lessons in the 3 courses, you can get them in the array of lessons. Using the relation will not mean that Yii will do more than one query, it simply mean that Yii present you the data in form of model.
If you want to get the 10 lessons, is better to select the lessons and use the relation to courses.
You can’t use together if the result should be displayed with pagination and you have a HAS_MANY relation. The reason for this is that LIMIT doesn’t make sense with HAS_MANY queries. The number of returned rows will not be equal to the number of entries in the main table. So you should use lazy loading instead (which is done automatically if you don’t set together)
Yii AR couldn’t do anything you can’t do in pure SQL neither. How should a SQL look like that limits the result set if you have many LEFT JOINEed tables per row? It’s not possible. Not even a view can help you here. Remember, for pagination you need LIMIT (inlcuding offset) to return only the results for a specific page.
What you can do though is to group by main table id to at least get the number of related Lessons. Anything else requires lazy loading of Lessons.
Another thing you could try is to use GROUP_CONCAT() to concatenate some information from all the Lessons that belong to one category. See here for some details:
Like i said: don’t set together to anything (not even false! just leave it away or set it to null) and it should use eager loading where possible and lazy loading for HAS_MANY. Have you tried it?
I have the same problem: two tables, HAS_MANY relation between the two, and I need to display a CListView of the joined results where some conditions are on both tables. The problem is that I need to have pagination on both.
Now, since of the HAS_MANY, a join must be inserted in the main query using the togetherotherwise Yii/MySQL explodes saying it doesn’t know about this column (which is the relationName.col_name). But here I just learned that I cannot use together in such cases where pagination is needed and the relation is HAS_MANY.
I’m not sure I fully understand the MySQL inner working here and the problem at hand. Isn’t the LIMIT used by MySQL after the resulted table has been calculated?
