Good tips. Thanks.
jason m wrote: > > > 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. > > > > -- View this message in context: http://n2.nabble.com/Converting-SQL-query-to-Cake-query-tp2434189p2440839.html 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 -~----------~----~----~----~------~----~------~--~---
