我该怎么写这个查询？

I'd like to write the following as a MySQL SELECT statement to cut down on the number of queries required to get the information, but I'm not sure how to write it.

我想将以下内容编写为MySQL SELECT语句,以减少获取信息所需的查询数量,但我不确定如何编写它。

I have two tables - tags and books_tags (a many-to-many relationship junction table). The final output I want would print as follows:

我有两个表 - tags和books_tags(多对多关系联结表)。我想要的最终输出将打印如下:

<label for="formFiltertag1"><input type="checkbox" name="tag[]" value="1" id="formFiltertag1" class="rank90" /> PHP (15)<br /></label>

Where the text is the name of the tag (tags.name) and the number in parens is the count of how often the tag's ID appears in the junction table (COUNT(books_tags.tag_id)). The input ID and value will be dynamic based on the tags.id field.

其中text是标记的名称(tags.name),而parens中的数字是标记ID在联结表中出现的频率计数(COUNT(books_tags.tag_id))。输入ID和值将基于tags.id字段动态显示。

I originally thought I'd just run a query that gets all of the info from the tag table and then use a foreach loop to run a separate count query for each one, but as they number of tags grows that could get unwieldy quickly.

我原本以为我只是运行一个查询来获取标签表中的所有信息,然后使用foreach循环为每个查询运行一个单独的计数查询,但随着标签数量的增加,可能会很快变得难以处理。

Here's an example as I have it written now (using CodeIgniter's ActiveRecord pattern)...

这是我现在编写的一个例子(使用CodeIgniter的ActiveRecord模式)...

The Model:

function get_form_tags() {
    $query = $this->db->get('tags');
    $result = $query->result_array();
    $tags = array();
    foreach ($result as $row) {
        $this->db->select('tag_id')->from('books_tags')->where('tag_id', $row['id']);
        $subResult = $this->db->count_all_results();
        $tags[] = array('id' => $row['id'], 'tag' => $row['tag'], 'count' => $subResult);
    }
    return $tags;
}

The controller:

function index() {
    $this->load->model('browse_model', 'browse');
    $tags = $this->browse->get_form_tags();

    $data['content'] = 'browse/browse';
    $data['tags'] = $tags;
    $this->load->view('global/template', $data);
}

The view (condensed):

视图(浓缩):

<?php foreach ($tags as $tag) : ?>
<label for="formFiltertag<?php echo $tag['id'] ?>"><input type="checkbox" name="tag[]" value="<?php echo $tag['id'] ?>" id="formFiltertag<?php echo $tag['id'] ?>" class="rank<?php echo $tag['count'] ?>" /> <?php echo $tag['tag'] . ' (' . $tag['count'] . ')' ?><br /></label>
<?php endforeach; ?>

This works, but like I've said it's going to create way more queries than needed to get the job done. Surely there's a better way. Penny for your thoughts?

这样可行,但就像我说的那样,它会创建比完成工作所需的查询更多的查询。当然有更好的方法。佩妮为你的想法?

Thanks much, Marcus

非常感谢,马库斯

2 个解决方案

#1

select t, coalesce(btc.Count, 0) as Count
from tags t
left outer join (
    select tagid, count(*) as Count
    from books_tags 
    group by tagid
) btc on t.tagid = btc.tagid

#2

$result returns an array of arrays, where array_combine() expects an array of strings.

$ result返回一个数组数组,其中array_combine()需要一个字符串数组。