what I have done (thanks to this post who helped a lot
http://mark-story.com/posts/view/using-bindmodel-to-get-to-deep-relations?utm_source=rss)
First of all, I`m doing this find in pagination, so there was a little
more to do.
First, I configured the $paginate var:
var $paginate = array (
'Comment' => array (
'limit' => 50,
'order' => 'Comment.id ASC',
'contain' => array('User(id, nick)', 'State(nick)')
)
);
Second, instead of using the Containable Behavior (wich is wonderfull
for 1 level of recursive findings, but really ugly for more levels), I
used the bind/unbindModel way, unbinding unecessarie relations and
forcing cake to use joins by seting foreignKey to false and providing
conditions:
$this->News->Comment->unbindModel(array('hasMany' => array('Thumb'),
'belongsTo' => array('News')), false);
$this->News->Comment->bindModel(
array('belongsTo' => array(
'User' => array('foreignKey' => false, 'conditions' => array
('User.id = Comment.user_id')),
'State' => array('foreignKey' => false, 'conditions' => array
('User.state_id = State.id'))
)), false);
$comments = $this->paginate('Comment', array('Comment.news_id' =>
$id));
This will produce this wonderfull single query, which gather al the
information I need:
SELECT `Comment`.`id`, `Comment`.`user_id`, `Comment`.`news_id`,
`Comment`.`body`, `Comment`.`created`, `Comment`.`modified`,
`Comment`.`thumbs`, `Comment`.`is_hided`, `User`.`id`, `User`.`nick`,
`State`.`nick` FROM `comments` AS `Comment` LEFT JOIN `users` AS
`User` ON (`User`.`id` = `Comment`.`user_id`) LEFT JOIN `states` AS
`State` ON (`User`.`state_id` = `State`.`id`) WHERE
`Comment`.`news_id` = 1 ORDER BY `Comment`.`id` ASC LIMIT 50
Just a note, for bind/unbindModel to work in the paginate methord you
need to set its second parameter to false.
Thats it.
On 28 mar, 10:58, Fábio "Kym" Nascimento <fhpnascime...@gmail.com>
wrote:
> Hello there, I just subscribed to this group, nice to met you guys
> 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