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 <[email protected]> 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-tp2434189p2437049.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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
