I have a doubt on how I should write my query on CakePHP. Instead of using the querybuilder from CakePHP I´d like to use the statement query. So I my query is:
SELECT `Post`.id, `Post`.title, COUNT(`Like`.id) AS `Posts_Liked`
FROM posts AS `Post`, likes AS `Like`
WHERE `Post`.id = `Like`.posts_id;
But when I send the result to the View page, I can´t get the count number when calling $post['post']['Posts_Liked'], so how should I call this data in the view?
Aggregation result will not be added to the data array as per the doc
You should either use virtual fields or get the data like this :
$post[0]['Posts_Liked']
you can inspect your data using
debug($post);
to see how your array is structured.
Using virtual fields you could achieve this like this:
$this->Post->virtualfields['Posts_liked'] = 0;
$this->Post->query('SELECT `Post`.id, `Post`.title, COUNT(`Like`.id) AS `Posts_liked` FROM posts AS `Post`, likes AS `Like` WHERE `Post`.id = `Like`.posts_id;');
and then get your data as per usual
$post['Post']['Posts_liked']
If you want it permanent, you should give a try to the counterCache
Try doing a pr($post); die(); right after making your model call, before it even gets to the view. I'm thinking it may be referenced by $post[0]['Posts_Liked'], since it's an aggregate function. You'll notice that CakePHP puts SUM(), MAX(), COUNT(), etc. in a $result[0]['count']; kind of notation. Hope this helps.
CakePHP's pr() function is much better than print_r, imho.
debug($result); die;in the controller and see if it is what you expecting