I think you should look into one of the methods using the Set class when combining results from a count query like this. I'm not sure exactly which set method you should use but maybe Set::combine.
http://book.cakephp.org/view/662/combine I think there is nothing wrong with using a foreach to combine the array, but it seems like the best practice is to use the Set class in place of for/foreach loops. I came across this kind of problem before, and sometimes if it's too complicated to do it the cake way, I end up just using regular php and sql queries. Nothing wrong with that. On Mar 7, 2:33 am, RyOnLife <ryan.mckil...@gmail.com> wrote: > Here's the afterFind(). It works. Would just like some comments on whether > this is a sound approach. > > function afterFind($results) { > if(!empty($results[0]['Page']) && > !empty($results[0][0]['total_view_count'])) { > $i = 0; > foreach($results as $result) { > $results[$i]['Page']['total_view_count'] = > $result[0]['total_view_count']; > unset($results[$i][0]); > $i++; > } > } > return $results; > } > > I'm much more unsure about the latter question of whether there is a place > in the Page model to do the bind. And now I'm wondering if it would be > possible and make sense to create a total_view_count.php file in the models > folder. > > > > > > RyOnLife wrote: > > > @grigri: Thanks. Just what I was looking for. I hadn't used bindModel() > > before, but now makes total sense on getting from the SQL to Cake query. > > > This does lead me to two follow up questions... > > > The $results array looks like this: > > > Array > > ( > > [0] => Array > > ( > > [Page] => Array > > ( > > [id] => 1 > > [user_id] => 1 > > [title] => test > > [slug] => test > > [created] => 2009-02-19 12:58:14 > > ) > > > [0] => Array > > ( > > [page_view_count] => 10 > > ) > > ) > > ) > > > If I want page_view_count in the Page array, is the best approach to > > manipulate it to my liking in the afterFind() callback in the Page model? > > > Since I am always going to want this 'page_view_count' data, I tried > > moving the bindModel() code to the beforeFind() callback in the Page > > model. Caused all sorts of problems, I imagine because beforeFind() gets > > triggered numerous times through the various associations between all the > > models. Should I stick to binding over and over in my controllers, or is > > there a way to do it in the models? > > > I ask both these questions because I'm anticipating a lot of redundant > > code with the array manipulation and binding. Something tells me there is > > a better way. > > > grigri wrote: > > >> Try this: > > >> $this->Page->bindModel(array( > >> 'hasOne' => array( > >> 'PageViewTotal' => array( > >> 'className' => 'PageView', > >> 'type' => 'inner' > >> ) > >> ) > >> )); > >> $results = $this->Page->find('all', array( > >> 'fields' => array('Page.*', 'COUNT(PageViewTotal.id) AS > >> page_view_count'), > >> 'group' => 'Page.id', > >> 'order' => 'page_view_count DESC', > >> 'limit' => 10 > >> )); > > >> hth > >> grigri > > >> On Mar 6, 3:36 pm, RyOnLife <ryan.mckil...@gmail.com> wrote: > >>> This doesn't work because 'view_count' is not a field. All of the view > >>> data > >>> is contained in the 'View' model, keyed to the 'Page' model (Post in > >>> your > >>> example). > > >>> Maybe it would help if I share the full details of my models, tables and > >>> the > >>> SQL query/result I would like to create in Cake: > > >>> Page hasMany PageView: > > >>> CREATE TABLE IF NOT EXISTS `pages` ( > >>> `id` int(11) NOT NULL auto_increment, > >>> `user_id` int(11) NOT NULL, > >>> `title` varchar(100) NOT NULL, > >>> `slug` varchar(100) NOT NULL, > >>> `created` datetime NOT NULL, > >>> PRIMARY KEY (`id`), > >>> FULLTEXT KEY `title` (`title`) > >>> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; > > >>> PageView belongsTo Page: > > >>> CREATE TABLE IF NOT EXISTS `page_views` ( > >>> `id` int(11) NOT NULL auto_increment, > >>> `page_id` int(11) NOT NULL, > >>> `user_id` int(11) default NULL, > >>> `user_ip` int(10) unsigned default NULL, > >>> `created` datetime NOT NULL, > >>> PRIMARY KEY (`id`) > >>> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED; > > >>> SQL query: > > >>> SELECT Page.*, COUNT(*) AS views FROM > >>> pages AS Page > >>> INNER JOIN page_views AS PageView ON Page.id = PageView.page_id > >>> GROUP BY Page.id > >>> ORDER BY COUNT(*) DESC > >>> LIMIT 10 OFFSET 0; > > >>> Returns these fields: > > >>> id, user_id, title, slug, created, views > > >>> The first five fields are those from the Page model and the sixth field, > >>> views, is a count of how many times the Page.id primary key is used as a > >>> PageView.page_id foreign key. > > >>> Thanks for the help. I know Cake is supposed to make SQL easier, but I > >>> am > >>> having trouble making the jump from SQL to Cake queries where the SQL is > >>> more involved than simple SELECT statements. > > >>> Stu-2 wrote: > > >>> > $topTen = $this->Post->find('list', > >>> > array( > >>> > 'fields' => array('Post.id', 'Post.view_count'), > >>> > 'order' => array('Post.view_count DESC'), > >>> > 'limit' => 10 > >>> > ) > >>> > ); > > >>> > this worked for me. > > >>> -- > >>> View this message in > >>> context:http://n2.nabble.com/Converting-SQL-query-to-Cake-query-tp2434189p243... > >>> Sent from the CakePHP mailing list archive at Nabble.com. > > -- > View this message in > context:http://n2.nabble.com/Converting-SQL-query-to-Cake-query-tp2434189p243... > Sent from the CakePHP mailing list archive at Nabble.com. --~--~---------~--~----~------------~-------~--~----~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
