Apologies for continuing to pound away at this thread, but I had another
issue come up. In addition to views, pages also have votes. When I tried to
count the votes and views, both counts are the same and are "wrong"—due to
the way the tables are being joined, the count doesn't work as desired.
My model association and find() query are pasted below. Any help on how to
do individual counts is welcomed.
So the Page model contains:
var $hasOne = array(
'PageViewTotal' => array(
'className' => 'PageView',
'type' => 'inner'
),
'PageVoteTotal' => array(
'className' => 'PageVote',
'type' => 'inner'
)
);
And the Cake query is:
$results = $this->Page->find('all', array(
'fields' => array(
'Page.*',
'COUNT(PageViewTotal.id) AS views',
'COUNT(PageVoteTotal.id) AS votes'
),
'group' => 'VideoList.id',
'order' => 'views DESC',
'limit' => 10,
));
RyOnLife wrote:
>
> 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 <[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-tp2434189p2437992.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
-~----------~----~----~----~------~----~------~--~---
