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