Below is my view. I wonder if my self-joins on people is what throws
the mysql PHP connector off so that it does not return the correct
table name?
Do you know what version of mysql you are using (the PHP module that
provides MySQL connectivity? Or are you using mysqli?
Mine is a few versions old (5.0.21), so I'm wondering if they didn't
add full view support until later versions.
CREATE OR REPLACE VIEW v_people AS
select p.peopleID,
CONCAT_WS('-', h.display_id, p.disp_order) display_id,
p.last_name,
p.first_name,
p.birthday) birthday,
TIMESTAMPDIFF(YEAR,p.birthday, CURDATE()) age,
CASE
WHEN h.phone_num1 <> '' THEN h.phone_num1
WHEN h.phone_num2 <> '' THEN h.phone_num2
WHEN p.phone1 <> '' THEN p.phone1
WHEN p.phone2 <> '' THEN p.phone2
WHEN p.phone3 <> '' THEN p.phone3
ELSE h.phone_num1
END primary_phone,
CASE
WHEN h.email_addr <> '' THEN h.email_addr
WHEN p.email1 <> '' THEN p.email1
WHEN p.email2 <> '' THEN p.email2
WHEN p.email3 <> '' THEN p.email3
ELSE h.email_addr
END primary_email,
p.user user,
p.password password,
p.permissions permissions,
p.household_id household_id,
p.person_id person_id,
p.person_id v_person_id,
p.status status,
p2.first_name dad_first_name,
p2.last_name dad_last_name,
p3.first_name mom_first_name,
p3.last_name mom_last_name
from people p inner join household h on ( h.household_id =
p.household_id )
left outer join people p2 on (p2.household_id = p.household_id
and
p2.rel_type in ('H','D','SD'))
left outer join people p3 on (p3.household_id = p.household_id
and
p3.rel_type in ('W','SM'))
where p.status not in('FM','FA');
On Dec 2, 4:04 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
wrote:
> Christoph,
>
> Note: in the following, I use the word "VIEW" in all caps to denote a
> database view rather than a Cake view, which is of coures something
> entirely different.
>
> I employed a MySQL (5+) VIEW in my first Cake project with no problem
> whatsoever. I simply defined a completely separate model based only on
> the VIEW, ensured that there were no field name conflicts, and then
> used it like any other model. No troubles. Following is my working
> example.
>
> Here is the VIEW:
> -----------------------------------------
> CREATE VIEW
> courses_students_teachers_scheduleblocks
> AS
> SELECT
> concat(courses_students.course_id,courses_students.student_id,courses.teacher_id,courses.scheduleblock_id)
> AS id,
> courses.scheduleblock_id AS scheduleblock_id,
> courses.id AS course_id,
> courses.subject AS subject,
> teachers.id AS teacher_id,
> teachers.teacher_fname AS teacher_fname,
> teachers.teacher_lname AS teacher_lname,
> students.id AS student_id,
> students.lname AS student_lname,
> students.fname AS student_fname
> FROM
> ((((courses_students join students) JOIN courses) JOIN teachers) JOIN
> scheduleblocks)
> WHERE (
> (courses.id = courses_students.course_id) AND
> (courses_students.student_id = students.id) AND
> (courses.teacher_id = teachers.id) AND
> (scheduleblocks.id = courses.scheduleblock_id)
> );
> -----------------------------------------
>
> Here is the model:
> -----------------------------------------
> class CoursesStudentsTeachersScheduleblock extends AppModel
> {
> var $name = 'CoursesStudentsTeachersScheduleblock';
>
> var $belongsTo = array(
> 'Scheduleblock' =>
> array('className' => 'Scheduleblock',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> 'Course' =>
> array('className' => 'Course',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> 'Teacher' =>
> array('className' => 'Teacher',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> 'Student' =>
> array('className' => 'Student',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> );
> -----------------------------------------
>
> Here is the controller:
> -----------------------------------------
> <?php
> class CoursesstudentsteachersscheduleblocksController extends
> AppController
> {
> var $name = 'Coursesstudentsteachersscheduleblocks';}?>
> -----------------------------------------
>
> Why did I create and use this VIEW? Simply because it simplified
> writing the joins in the associations. Without the VIEW, everything
> became very complicated, so that I had trouble following my own SQL.
>
> And everything worked exactly as advertised. I assumed at the time that
> Cake never knew that my table was actually a VIEW. But if Cake did
> know, why should it treat my VIEW any differently from an ordinary
> table?
>
> In your problem situation, are you certain there were no collisions or
> mistakes in the SQL tables, VIEWs and field names??
>
> Regards,
>
> Ralph
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Cake PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
