CakePHP查询和JSON输出 - 改进当前的解决方案

I'm coding a tagging system in CakePHP (latest release) but the solution I have made seems over complicated compared to how easy the rest of CakePHP is. Hopefully someone can point me in the right direction or help me improve my current solution.

I need to pass to my view data in this format (JSON):

[{label:'actionscript', count: 14}, {label:'php', count: 2} ... ]

Now I know the following SQL query gives me the needed data:

SELECT tags.name, count(*)
FROM tags
RIGHT JOIN tags_users
ON tags.id=tags_users.tag_id
GROUP BY name";

So using CakePHP find method I made:

$options = array();
$options['contain'] = '';   
$options['recursive'] =  -1;            
$options['joins'][0]['table'] = 'tags_users';
$options['joins'][0]['type'] = 'RIGHT';
$options['joins'][0]['conditions'] = 'Tag.id = tags_users.tag_id';
$options['fields'] =  array('name', 'COUNT(*) as tag_counter');
$options['group']  =  'Tag.name';
$options['order']  =  'tag_counter DESC';       
$availableTagsArray = $this->User->TagsUser->Tag->find('all', $options);

This give me an array out as:

Array
(
[0] => Array
    (
        [Tag] => Array
            (
                [name] => actionscript
            )

        [0] => Array
            (
                [tag_counter] => 14
            )

    )

[1] => Array
    (
        [Tag] => Array
            (
                [name] => php
            )

        [0] => Array
            (
                [tag_counter] => 2
            )

    )
)

I then convert this array to JSON by:

$availableTags = "[";

foreach ($availableTagsArray as $tag) {
        $availableTags .= "{label:'{$tag['Tag']['name']}', count:{$tag[0]['tag_counter']}},";   
    }

$availableTags = substr($availableTags, 0, -1);
$availableTags .= "]";  
$this->set("availableTags", $availableTags);

[UPDATE]

After doing a lot of research and using both answers. I've found there multiple ways to do this and that includes my original solution. You could also use virtual fields then the afterFind method and flattening the results there.

You can use the Containable Behavior to simplify your query and a JSON view to simplify your output.

You don't need to write your own loop to manually output a JSON string from an array. PHP already has a function to encode arrays as JSON:

http://php.net/manual/en/function.json-encode.php

You just need to make sure the array going in has the correct keys so that the JSON is labelled properly.

Also read into setting up your model associations properly, then you can use the containable behavior within your find options to get the tags without having to manually specify joins, which is usually messy and complicated.