I was overthinking the bind. Instead of bindModel() in the controller, just
needed the following association in the Page model:

  var $hasOne = array( 
    'PageViewTotal' => array( 
      'className' => 'PageView', 
      'type' => 'inner' 
    )
  );

Between that and the afterFind() callback, seems to do the trick and be a
fairly pure solution. Still seems like if the models were set up correctly,
I could get page_view_count in the Page dimension of the array without
having to manipulate it there using afterFind().

As I am trying to learn, any comments as to the effectiveness of this
solution and/or ways to improve would be much appreciated. 



RyOnLife 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-tp2434189p2437599.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
-~----------~----~----~----~------~----~------~--~---

Reply via email to