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