Re: Modeling database views
> It is a known fact amongst developers that source code tends to have as much > sensitivity as humans do. Some times your loop may be in a good mood, and > work like a charm; while others it may remember that last time you smashed > the keyboard... And the code is like: "oh yeah, that's gonna help, it will > probably make me work SOOO much better... NOT!" > Sorry, lack of sleep ;) No, I think you are absolutely dead on and makes absolute sense now that I think about it. :p thnx, Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
RE: Modeling database views
It is a known fact amongst developers that source code tends to have as much sensitivity as humans do. Some times your loop may be in a good mood, and work like a charm; while others it may remember that last time you smashed the keyboard... And the code is like: "oh yeah, that's gonna help, it will probably make me work SOOO much better... NOT!" Sorry, lack of sleep ;) -MI --- Remember, smart coders answer ten questions for every question they ask. So be smart, be cool, and share your knowledge. BAKE ON! -Mensaje original- De: cake-php@googlegroups.com [mailto:[EMAIL PROTECTED] En nombre de Christoph Enviado el: Jueves, 30 de Noviembre de 2006 08:49 p.m. Para: Cake PHP Asunto: Re: Modeling database views The first time I tried to model one of my views (using bake.php), I got the same error. However, when I came back to it several hours later, it seemed to work. *shrug* I don't know what happened and why it worked the second time. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Modeling database views
> How did you get that to work? What version of Cake are you using? I'm used the version downloadable from the CakePHP site > 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 - The first time I tried to model one of my views (using bake.php), I got the same error. However, when I came back to it several hours later, it seemed to work. *shrug* I don't know what happened and why it worked the second time. thnx, Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Modeling database views
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
> --
>
Re: Modeling database views
I think we have beat this to death... but I will add a couple more
things.
> I agree on general principle. My question is about the wisdom of using
> db views within cake because it breaks the relationship model (for the
> want of a better term). And I'm not sure how good or bad that is.
Using a view does not break the relationship model, it simply as
another model to the relationship.
> 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.
It is possible to update/add records in some views. It depends on the
database, and the view. However, I would highly discourage this!
One very compelling reason to use (or not to use) a view, is
performance. Using Ralph's view example; It is probably faster to use a
view and let MySQL do the 5 joins and return the result, as opposed to
cake making 6 database calls. However, keep in mind that you might not
need all 5 joins every time. Using cake, you can unbind your
relationships at run time, reducing extraneous database work. But the
view will have the overhead of all five joins every time it is
accessed.
Of course my blind performances comparisons may not apply to your
situation. It would depend on the number of records, number of indexes,
types of data, hardware architecture, and the database server.
Finally, in as much is a view is only a convenience, it should only be
used if it simplifies your project. If you find your self writing a
complex join, and using that sql through out your project, then it
might be a good idea to take that complexity and put it in a convenient
central location (the database).
sc
On Nov 19, 2:24 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
wrote:
> I used a sql VIEW as the basis of an ordinary model in 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 corresponding 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' => '',
>
Re: Modeling database views
I used a sql VIEW as the basis of an ordinary model in 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 corresponding 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' => ''),
);
}
--
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 Cake model apparatus 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
Re: Modeling database views
> I can't see any reason why you wouldn't want to model a view. As far as > SQL is concerned, views can be treated just like any other table. So > all of the fancy helper methods should work the same on views as they > do on tables. I agree on general principle. My question is about the wisdom of using db views within cake because it breaks the relationship model (for the want of a better term). And I'm not sure how good or bad that is. Let me explain. You have a table. Say 'users'. you have a model called User, a controller called UsersController and views for each action. The UsersController "uses", say, the Profile, in addition to the User, model to allow cake to get associated an profile. It might also "use", say, a model called Skill to get a users associated skills. This is all well and good. And when you use, say, findAll(), you are returned an array of all the related data. Ok. So if you wanted to show an index view of the users in the db, the UsersController would use it's own model to retrieve all that data. I hope I'm not being too convoluted here but I think I am. In any case, but modeling and using the database view set up to aggregate all the user information (which may include joined data from the skills and/or profiles tables), the UsersController is no longer using it's own model. It's using a 'virtual' (for the want of a better word) model that is _based_ on the users table but isn't a direct representation of the data within that table. I'm just not sure if this goes outside the theoretical structure that is MVC. A model uses a controller (and vice versa) to create a presentation. By using a db view, you essentially have X models (the various db views) using a controller to create a presentation. And again, being so new to MVC, I'm not sure if this is straying beyond those theoretical boundries. I hope I'm making sense. thnx, Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Modeling database views
I don't have any experience with database view in cakePHP. But I have used both, so here's my take. I can't see any reason why you wouldn't want to model a view. As far as SQL is concerned, views can be treated just like any other table. So all of the fancy helper methods should work the same on views as they do on tables. sc --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Modeling database views
On 11/17/06, Christoph <[EMAIL PROTECTED]> wrote: > > > I believe that the point of the helper functions is to keep things > > database-agnostic, and building in exceptions for a specific database > > should be kept to a minimum. > > I'm not talking about creating helpers or helper functions. Just using > a DB view's model for things like findAll(), generateList(), etc. > Consider the following: > I think we misunderstood each other here. :) I think database views are database-specific enough that you would have a hard time creating "generic" database view handlers. That's all. I've never used views, but obviously they have a time and a place. -- Chris Hartjes "The greatest inefficiencies come from solving problems you will never have." -- Rasmus Lerdorf @TheBallpark - http://www.littlehart.net/attheballpark @TheKeyboard - http://www.littlehart.net/atthekeyboard --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Modeling database views
> I believe that the point of the helper functions is to keep things > database-agnostic, and building in exceptions for a specific database > should be kept to a minimum. I'm not talking about creating helpers or helper functions. Just using a DB view's model for things like findAll(), generateList(), etc. Consider the following: Table: games Fields: id, game_name Table: sets Fields: id, set_name Table: game_sets Fields: id, set_id, game_id Table: game_pieces Table: id, game_set_id, piece_name Using just the game_pieces model, if I use findAll(), generateList(), et. al., I get only the game_set_id and would have to look elsewhere (in the array returned by, say, findAll()) to get the set name or even the game name. Not in and of itself a bad thing but if you have thousands and thousands of game pieces, each getting returned with the related game_sets, games, sets, that's a massively huge array that you'll be getting back. Now, lets consider the following view (which will have it's own Cake model) CREATE VIEW game_pieces_view AS SELECT game_pieces.id, game_pieces.piece_name, games.game_name, sets.set_name FROM game_pieces INNER JOIN game_sets ON games_sets.id = game_pieces.game_set_id INNER JOIN sets ON sets.id = game_sets.set_id INNER JOIN games ON games.id = game_sets.game_id so now I can do $this->GamePiecesView->findAll() or $this->GamePiecesView->generateList() and have easy access to access to just the data I need. I'll be doing all of this in the game_pieces_controller in, for example, the index action method. This is a departure since the controller wouldn't be using the corresponding model but instead a related model (ie, GamePiecesView model and not the GamePieces model). I'd still be using the GamePieces model for other things (for example saving the data from add/edit) but for general wholesale listing of data (for example, the output from the index action), I'd be using the GamePiecesView model. Because of that departure and because I'm so new to MVC in general, I'm not sure if it's good practice. This is the reason for my original question. thnx, Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Modeling database views
On 11/17/06, Christoph <[EMAIL PROTECTED]> wrote: > > Any thoughts? > > thnx, > Christoph I have zero experience with views...but I don't see what would stop anyone from creating a model that manipulates views. Maybe you don't get to use all the fancy helper methods, but I suspect you probably need to have a certain level of knowledge of SQL and databases in order to get the most out of views. Which, by extension, means you should be okay doing your own hand-written queries. I believe that the point of the helper functions is to keep things database-agnostic, and building in exceptions for a specific database should be kept to a minimum. Just my opinion. -- Chris Hartjes "The greatest inefficiencies come from solving problems you will never have." -- Rasmus Lerdorf @TheBallpark - http://www.littlehart.net/attheballpark @TheKeyboard - http://www.littlehart.net/atthekeyboard --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Modeling database views
Any thoughts? thnx, Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Modeling database views
I'm curious what the general consensus in the CakePHP community is on modeling database views? Is it a good idea? Not? What about creating a controller and/or a view for the, uh, view? I'm not asking about the wisdom of using database views. In general, everyone knows they are a good way of aggregating data for presentation. I'm asking about the wisdom of using them within the cake framework. Should they not be modeled and just be specifically queried (using query() or execute())? thnx, Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
