cakephp's addicts.
I'm relatively new to cakephp, like 3-4 months but I just love it,
after 3 years developing solo php, the famous "spaghetti code", start
using and learning cake was just wonderfull. Just a point here, I
heard a lot about how cake documentation sucks. Of course I think it
could be better, but it doenst just suck at all, easier or harder,
sooner or later I was always able to find what I needed, until now.
My scenario is the following:
News hasMany Comment
Comment belongsTo User
User belongsTo State
Its a very simple scenario, where I need to find all comments for a
given news and for each comment I want to know its User.nick and its
User.State.nick. Using the Containable behavior its very simple:
$this->Comment->contain('User.id', 'User.nick', 'User.country_id',
'User.State.id', 'User.State.nick');
or
$this->Comment->contain
('User' => array('fields' => array('User.id', 'User.nick',
'User.state_id'),
'State' => array('fields' => array('id', 'nick'))));
****or.. there are many sintaxes you can use, I have tried then all
$comments = $this->Comment->find('all', array('conditions' =>
'Comment.news_id' => 1
So far so good, this could be achieved with only 1 query, Comment left
join Users and User left join States, BUT, even if cakephp finds and
returns what I need, its done with a lot more queries, heres what
cakephp does:
First, cake selects all comments and its users information:
SELECT `Comment`.`id`, `Comment`.`user_id`, `Comment`.`news_id`,
`Comment`.`body`, `Comment`.`created`, `Comment`.`modified`,
`Comment`.`thumbs`, `Comment`.`is_hided`, `User`.`id`, `User`.`nick`,
`User`.`state_id` FROM `comments` AS `Comment` LEFT JOIN `users` AS
`User` ON (`Comment`.`user_id` = `User`.`id`) WHERE
`Comment`.`news_id` = 1 ORDER BY `Comment`.`id` ASC LIMIT 50
Then, it selects comment by comment, user by user, its state
information:
SELECT `User`.`id`, `User`.`nick`, `User`.`state_id` FROM `users` AS
`User` WHERE `User`.`id` = 1
SELECT `State`.`id`, `State`.`nick` FROM `states` AS `State` WHERE
`State`.`id` = 25
I have a page with 50 comments, that means 100 extra more queries to
retrieve the State.nick. Imagine a scenario where 100 users are doing
this ate the very same time, its 1000 more queries in a second.
After I detected that, I really cant understand why find just doesnt
LEFT JOIN the States table too and eliminate all those unecessarie
queries. Here what I expected from find:
SELECT `Comment`.`id`, `Comment`.`user_id`, `Comment`.`news_id`,
`Comment`.`body`, `Comment`.`created`, `Comment`.`modified`,
`Comment`.`thumbs`, `Comment`.`is_hided`, `User`.`id`, `User`.`nick`,
`User`.`state_id`, `State`.`nick`
FROM `comments` AS `Comment`
LEFT JOIN `users` AS `User`
ON (`Comment`.`user_id` = `User`.`id`)
LEFT JOIN `states` AS `State`
ON (`State`.`id` = `User`.`id`)
WHERE `Comment`.`news_id` = 1
ORDER BY `Comment`.`id` ASC
I'm not a PHP guru, but i tried to read the find's soure code, unhappy
I cant still realize why thats happening. And this happens a lot,
anytime you try to find deep models more than 1 level, that happens,
the find method is able to return what I expect, but very far from the
best way.
I thought about use my custom query, but I dont think its the best
way. And this is a very critical problem, any project that have a lot
of users access will suffer because of that.
Sorry for my bad english, but does anyone have a clue, a tip, or
something that might help? Is there a way to do this that the find
method is forced to JOIN tables instead of generating single queries
to fill the results?
Thanks in advance
Kym
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
No comments:
Post a Comment