CakePHP - 查找和计算有多少个关联记录 [英] CakePHP - Find and count how many associated records exist

问题描述

我尝试执行搜索，作为条件，它必须只返回具有一定数量的关联记录的记录。

I'm trying to perform a search which as a condition it must return only records with a certain amount of associated records.

目前我有一个模型（乐队）和一个相关联的模型thourgh hasmany（Songids）。

Currently I have one model (Bands) and one associated model thourgh hasmany (Songids). Each band can have many songids.

在此特定搜索中，我想返回4个结果，其中这些频段至少 10个关联的歌曲。

In this particular search I want to return 4 results of bands where those bands have at least 10 associated songids.

我试图在HAVING条件下在findid过程中执行此操作，并在具有COUNT（Songid.band_id）的songid模型中执行虚拟字段，但它只返回

I tried to perform this with "HAVING" condition in the find procedure and virtualfields in the songid model with "COUNT(Songid.band_id)" but it only returns the total, not just for the selected field.

我如何实现这一目标？我在想如下：

How can i achieve this? I was thinking of something like:

    $random = $this->Band->find('all', array(
            'fields' => array('Band.band'),
            'order' => 'rand()',
            'limit' => 4,
            'contain' => array(
                'Songid' => array(
                    'conditions' => array(
                        'band_id COUNT' => 2)
                )
            )
        )
    );

最后部分'band_id COUNT'=> 2应该做的伎俩，但我不能这行得通。 任何想法？

Last part "'band_id COUNT' => 2" should do the trick but I can't make it work. Any ideas?

编辑：我可以做一个找到所有，然后一个foreach与计数每个相关联的模型，但我正在寻找一个找到，没有处理的解决方案。

I could do a find all and then a foreach with a count for each associated model, but i'm looking for a "one find, no processing" solution.

推荐答案

'band_id COUNT'=> 2 不是CakePHP ORM理解的东西，如果你必须使用一个合适的SQL片段，例如 COUNT（table）> = x

'band_id COUNT' => 2 is not something the CakePHP ORM would understand, if at all you'd have to use a proper SQL snippet like COUNT(table) >= x.

但条件总是映射到 WHERE ，并且 WHERE COUNT 在MySQL中不起作用（您可能已经知道，如使用 HAVING ）。

However conditions always map to WHERE, and WHERE COUNT doesn't work in MySQL (which you are probably aware of as you've mentioned using HAVING).

离开...

在这种情况下，您可以使用 计数器缓存 字段

In this particular situation you could make use of a counter cache field

class Songid extends AppModel {
    // ...

    public $belongsTo = array(
        'Band' => array(
            'counterCache' => true,
        )
    );

    // ...
}

可以使用简单的条件，例如：

that way you can easily query by the number of associated records using a simple condition like:

$this->Band->find('all', array(
    // ...
    'conditions' => array(
        'Band.songid_count >=' 10
    )
));

使用连接

计数器，您必须手动加入相关联的表，因为它们不会自动包含，而是在单独的查询中使用。

Use joins

When not using a counter, you'll have to join in the associated tables manually as they are not included automatically, but rather used in separate queries.

• strong> 查询出错跨插两个模型的插件 - CakePHP


• http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables

• Error with a query across two Models of a Plugin - CakePHP
• http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables

加入后，您可以安全地查询相关联的模型：

Once joined in you can safely query on the associated model:

$this->Band->find('all', array(
    'joins' => array(
        array(
            'table' => 'songids',
            'alias' => 'Songid',
            'type' => 'LEFT',
            'conditions' => array('Band.id = Songid.band_id')
        )
    ),
    // ...
    'group' => 'Band.id HAVING COUNT(Songid.id) >= 10'
));

这篇关于CakePHP - 查找和计算有多少个关联记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！