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