如何仅选择具有至少一个关联记录的那些记录？ [英] How to select only those records that have at least one associated record?

问题描述

我有以下关联：订单归属于网站

I have the following associations: Orders belongsTo Sites

我执行以下查询：

$sites = $this->Sites->find('all')
->select(['id', 'name'])
->contain([
        'Users' => function ($q) {
                return $q->select(['id', 'owner_id', 'firstname', 'lastname']);
        },
        'Orders' => function ($q) {
                return $q->where(['status >' => 0]);
        },
        'Orders.Agplans'
])
->matching('Users', function ($q) use($owner_id)
{
    return $q->where([
        'Users.owner_id' => $owner_id
    ]);
})
->all();

但是大多数站点都没有订单，所以我得到了某种结果：

But most of the sites have no orders so I get some kind of results:

(int) 100 => object(App\Model\Entity\Site) {
    'id' => (int) 7966,
    'name' => 'Site example',
    'orders' => [],
    'users' => [
        (int) 0 => object(App\Model\Entity\User) {

    ...

是否可以在查询中指定我只希望个站点而不包含空的订单？ p>

Is it possible to specify in the query that I only want sites with not empty orders?

推荐答案

由于您拥有的是 hasMany 关联，因此您将拥有除了包含订单以外，还要采取其他措施。

Since what you have there is a hasMany association, you'll have to take measures additionally to containing Orders.

您可以使用 INNER 联接，它将仅选择存在匹配顺序的那些行。出于过滤目的，可以使用 Query :: innerJoinWith（）轻松完成此操作，这类似于 Query :: matching（），但不会加载任何结果。

You could use an INNER join, that would select only those rows where a matching order is present. For filtering purposes, this can easily be done using Query::innerJoinWith(), which is similar to Query::matching(), but it won't load any results.

$sites = $this->Sites
    ->find('all')
    ->select(['id', 'name'])

    // ...

    ->innerJoinWith('Orders', function ($q) {
        return $q->where(['Orders.status >' => 0]);
    })
    ->group(['Sites.id']) // may or may not be necessary
    ->all();

像以前一样使用匹配项

您已经在对个用户使用 Query :: matching（），所以我假设您知道它的作用...过滤。也可以为 Orders 添加进一步的匹配项，但是它将获取其他数据。

Use matching like you already do

You are already using Query::matching() for Users, so I'd assume that you know what it does... filtering. Adding a further matching for Orders is an option too, it will however fetch additional data.

$sites = $this->Sites
    ->find('all')
    ->select(['id', 'name'])

    // ...

    ->matching('Orders', function ($q) {
        return $q->where(['Orders.status >' => 0]);
    })
    ->group(['Sites.id']) // may or may not be necessary
    ->all();

最后但并非最不重要的是，使用计数器缓存

使用正确索引的计数器缓存可能是效果最好的选项，因为过滤将通过同一表中的一列进行。

Last but not least, use a counter cache

Using a properly indexed counter cache is probably the best performing option, as filtering would be done via a column in the same table.

您可以在 Orders 模型中使用适当的条件对其进行定义，然后只需使用 where（）在您的查询中，例如

You'd define it in your Orders model with the proper conditions, and then just use where() in your query, like

$this->addBehavior('CounterCache', [
    'Sites' => [
        'active_orders_count' => [
            'conditions' => ['Orders.status >' => 0]
        ]
    ]
]);

$sites = $this->Sites
    ->find('all')
    ->select(['id', 'name'])

    // ...

    ->where([
        'active_orders_count >' => 0
    ])
    ->all();

另请参见

• Cookbook>数据库访问& ORM>查询生成器>使用innerJoinWith


• 菜谱>数据库访问和ORM>查询生成器>按关联数据过滤


• Cookbook>数据库访问& ORM>行为>计数器缓存



See also

• Cookbook > Database Access & ORM > Query Builder > Using innerJoinWith
• Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data
• Cookbook > Database Access & ORM > Behaviors > Counter Cache

这篇关于如何仅选择具有至少一个关联记录的那些记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！