How did you get that to work? What version of Cake are you using?
I've got one view that I am using with an older version of cake
(0.9.x). But I'm having fits upgrading cake because the underlying
mysql_fetch_field() function calls in dbo_source keep trying to tell
cake that the view has no table name -
So I get all kinds of errors - or at least some array structures that
have the wrong model names.
I really don't want to have to take the performance hit of using the
model association queries for every one of my queries.
Thanks,
Steve T.
On Nov 19, 2:24 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
wrote:
> I used a sql VIEW as the basis of an ordinarymodelin my first Cake
> project. It turned out to be helpful in reducing the complexity of some
> queries, and just generally making things easier to understand. I think
> VIEWs are a useful tool to use, but not a panacea for anything in
> particular. Whatever can be accomplished with a VIEW can also be
> accomplished without a VIEW, as far as I can see.
>
> I don't agree that sql VIEWs would necessarily be non-generic. An
> unadorned VIEW is pretty standard, I think.
>
> Here's an example of a VIEW definition in Mysql. I've added extra blank
> lines to clarify the structure. This particular VIEW is conceptually
> quite simple -- just a multi-table natural join -- but unfortunately
> SQL has a way of making things complicated.
>
> In fact, that's a good reason to consider using VIEWs. Complexity is
> hidden inside them, potentially making other parts of the the
> application simpler.
>
> ---------------------------------------------------------------------------------------------------
>
> 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));
>
> ---------------------------------------------------------------------------------------------------
>
> And here is the correspondingmodel:
>
> ---------------------------------------------------------------------------------------------------
>
> 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' => ''),
>
> );
>
> }------------------------------------------------------------------------------
>
> Notes:
>
> 1) Mysql version 5 is necessary for using VIEWs. Version 4 doesn't
> support them.
>
> 2) One has to create a primary key (preferably named id) in order for
> the usual Cakemodelapparatus to work. For a sql VIEW, this has to be
> done by including one or more primary keys in the field list for the
> VIEW. In the example, note that we concatenate several individual
> foreign keys to form a single primary key named id for the VIEW:
>
> ------------------
> select concat(courses_students.course_id, courses_students.student_id,
> courses.teacher_id, courses.scheduleblock_id) AS id
> ------------------
>
> 3) As we did above for the primary key, we use field name aliases to
> specify the VIEW's interface to the outside world. Thus in the above
> definition of the primary key, other code that uses the VIEW will not
> be able to see courses_students.course_id or any of the other three
> components of the key. Only the composite key named id will be visible
> to the outside world, and it will be unique over all the possible
> (pseudo) rows of the VIEW.
>
> 4) It is not possible insert records into the VIEW, and I don't think
> updating the VIEW would ever be possible or even make sense.
>
> I hope the above is helpful. Any comments, especially corrections,
> would be appreciated.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Cake PHP" group.
To post to this group, send email to cake-php@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
