var $paginate =
array(
'Assignmentrequest' => array(
'recursive' => 1,
'fields' => array('*'),
'joins' => array(
array(
'table' => 'families',
'alias' => 'Family',
'type' => 'inner',
'conditions'=> array('Family.id =
Assignmentrequest.family_id')
),
array(
'table' => 'families_services',
'alias' => 'FamiliesService',
'type' => 'inner',
'conditions'=> array('FamiliesService.family_id =
Family.id')
)
),
'contain' => array('Assignmentrequest_slot'),//,
'Assignmentrequest_slot.Babysitter'),
'limit' => 5,
'order' => array('Assignmentrequest.id' => 'asc')
)
which has the same result, but feels a bit cleaner to me..
I can not find the original article that discusses this..
(CakePHP 1.2 or higher)
On Mar 29, 12:51 am, Fábio "Kym" Nascimento <fhpnascime...@gmail.com>
wrote:
> Oh well, I found a solution for those who might be interested heres
> what I have done (thanks to this post who helped a lothttp://mark-story.com/posts/view/using-bindmodel-to-get-to-deep-relat...)
>
> 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