I found out some things that make enough sense to work around:
1) If I remove the _ from the name of the view it works much better
(v_people changed to vpeople)
2) This view works fine when accessed via associations from other
models. (ie, when from a groups model via a HABTM association with
Person.
3) But - this view returns any computed fields under strange array keys
when accessed directly from a Person->findAll() function call.
So apparently cake knows enough when using associations to name the
associated array keys correctly - but when using a direct
Model->findAll() call it apparently relies on the results of
mysql_fetch_field() to tell it the table name, which it can't.
So to get around this, I ended up adding an association and code to
re-calculate the 2 fields into the cake view code for my one action
that needed to read the Person table directly.
Ugly - but it works and should be somewhat future proof.
Maybe this info might help someone else banging their head against
using database VIEWS in cake.
Steve T.
On Dec 4, 10:43 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
