排除 CakePHP 中 HABTM 关系的空结果

发布于 2024-12-12 03:13:07 字数 1373 浏览 1 评论 0原文

大多数 CakePHP 文档似乎告诉您如何根据具体关系结果进行过滤。我似乎找不到的是如何过滤掉具有不返回数据的关系的结果。

例如,以具有帖子和标签的典型博客为例。标签拥有并属于许多帖子 (HABTM)。对于此讨论,假设采用以下表结构:

posts ( id, title )
tags ( id, name )
posts_tags ( post_id, tag_id )

如何仅查找具有一个或多个与之关联的帖子的标签(即排除不会返回帖子的标签)?

理想的结果集看起来像这样(为格式添加引号):

Array (
    [0] => Array (
            [Tag] => Array (
                      [id] => 1
                      [name] => 'Tag1' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 1
                              [title] => 'Post1' )
                      [1] => Array (
                              [id] => 4
                              [title] => 'Post4' ) )
    )
    [1] => Array (
            [Tag] => Array (
                      [id] => 4
                      [name] => 'Tag5' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 4
                              [title] => 'Post4' )
                      [1] => Array (
                              [id] => 5
                              [title] => 'Post5' )
                      [2] => Array (
                              [id] => 6
                              [title] => 'Post6' ) )
    ) )

Most of the CakePHP documentation seems to tell you how to filter based on a concrete relationship result. What I cannot seem to find is how to filter out results which have a relationship that returns no data.

For example, take the typical blog example which has Posts and Tags. Tags has and belongs to many Posts (HABTM). For this discussion, assume the following table structure:

posts ( id, title )
tags ( id, name )
posts_tags ( post_id, tag_id )

How do you find only the Tags which have one or more Posts associated with them (i.e. exclude Tags which would return no Posts)?

The ideal result set would look something like (quotes added for formatting):

Array (
    [0] => Array (
            [Tag] => Array (
                      [id] => 1
                      [name] => 'Tag1' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 1
                              [title] => 'Post1' )
                      [1] => Array (
                              [id] => 4
                              [title] => 'Post4' ) )
    )
    [1] => Array (
            [Tag] => Array (
                      [id] => 4
                      [name] => 'Tag5' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 4
                              [title] => 'Post4' )
                      [1] => Array (
                              [id] => 5
                              [title] => 'Post5' )
                      [2] => Array (
                              [id] => 6
                              [title] => 'Post6' ) )
    ) )

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

野侃 2024-12-19 03:13:07

我发现以可靠的方式做到这一点的唯一方法是使用 临时连接。使用它们,您可以指定内部联接类型并获得您想要的结果。

The only way I've ever found to do this in a reliable way is to use ad hoc joins. Using these, you can specify an inner join type and get exactly what you want.

浅笑依然 2024-12-19 03:13:07

以下是用 Cake 1.3 进行测试的。

首先,您可能想要或已经在模型上定义了 HABTM 关系,以应对通常适用的所有其他情况:

class Post extends AppModel {
    var $hasAndBelongsToMany = 'Tag';
}

class Tag extends AppModel {
    var $hasAndBelongsToMany = 'Post';
}

根据 Cake 自己的文档:[1]

在 CakePHP 中,一些关联(belongsTo 和 hasOne)执行自动联接来检索数据,因此您可以发出查询来检索模型
基于相关数据。

但 hasMany 和 hasAndBelongsToMany 关联的情况并非如此。这是
强制连接可以解决这个问题。您只需定义必要的连接
组合表并获得查询所需的结果。

排除空的 HABTM 结果就是其中之一。蛋糕书的同一部分解释了如何实现这一点,但通过阅读文本,我并没有发现结果实现这一点过于明显。在 Cake Book 的示例中,他们使用\连接路径 Book ->图书标签 -> Tags,而不是我们的Tag->帖子标签 ->帖子。对于我们的示例,我们将在 TagController 中进行如下设置:

$options['joins'] = array(
    array(
        'table'      => 'posts_tags',
        'alias'      => 'PostsTag',
        'type'       => 'INNER',
        'foreignKey' => false,
        'conditions' => 'PostsTag.tag_id = Tag.id'
    ),
    array(
        'table'      => 'posts',
        'alias'      => 'Post',
        'type'       => 'INNER',
        'foreignKey' => false,
        'conditions' => 'Post.id = PostsTag.post_id'
    )
);

$tagsWithPosts = $this->Tag->find('all', $options);

确保将foreignKey 设置为 false。这告诉 Cake 它不应尝试找出连接条件,而应仅使用我们提供的条件。

由于连接的性质,这通常会带回重复的行。要减少返回的 SQL,请根据需要在字段上使用 DISTINCT。如果您希望所有字段都像 find('all') 通常返回的那样,这会增加您需要对每一列进行硬编码的复杂性。 (当然,您的表结构不应该经常更改,但它可能会发生,或者如果您可能只有很多列)。要以编程方式获取所有列,请在 find 方法调用之前添加以下内容:

$options['fields'] = array('DISTINCT Tag.'
                   . implode(', Tag.', array_keys($this->Tag->_schema)));
// **See note

请务必注意,HABTM 关系在主选择之后运行。本质上,Cake 获取符合条件的标签列表,然后运行另一轮 SELECT 语句来获取关联的帖子;您可以从 SQL 转储中看到这一点。我们手动设置的“连接”适用于第一个选择,为我们提供所需的标签集。然后内置的 HABTM 将再次运行,为我们提供与这些标签相关的所有帖子。我们不会有任何没有帖子的标签,这是我们的目标,但我们可能会得到与标签相关的帖子,这些帖子不属于我们的任何初始“条件”(如果添加了它们)。

例如,添加以下条件:

$options['conditions'] = 'Post.id = 1';

将产生以下结果:

Array (
    [0] => Array (
            [Tag] => Array (
                      [id] => 1
                      [name] => 'Tag1' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 1
                              [title] => 'Post1' )
                      [1] => Array (
                              [id] => 4
                              [title] => 'Post4' ) )
    )
)

根据问题中的示例数据,只有 Tag1 与我们的“条件”语句关联。所以这是“连接”返回的唯一结果。然而,由于 HABTM 在此之后运行,因此它抓取了与 Tag1 关联的所有帖子(Post1 和 Post4)。

快速提示 - 在 Model::find() 中进行临时连接。本文还展示了如何推广该技术并将其添加到扩展 find() 的 AppModel 中。

如果我们真的只想看到 Post1,我们需要添加一个 'contain'[2< /a>] 选项子句:

$this->Tag->Behaviors->attach('Containable');
$options['contain'] = 'Post.id = 1';

给出结果:

Array (
    [0] => Array (
            [Tag] => Array (
                      [id] => 1
                      [name] => 'Tag1' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 1
                              [title] => 'Post1' ) )
    )
)

您可以使用 bindModel 来重新定义与 find() 实例的 HABTM 关系,而不是使用 Containable。在bindModel中,您将添加所需的后置条件:

$this->Tag->bindModel(array(
    'hasAndBelongsToMany' => array(
        'Post' => array('conditions' => 'Post.id = 1'))
    )
);

我觉得对于试图了解蛋糕的自动魔法能力的初学者来说,使显式连接更容易看到和理解(我知道这适合我)。另一种有效且可以说更“蛋糕”的方法是专门使用 unbindModel 和 bindModel。 Teknoid 在 http://nuts-and-bolts-of-cakephp.com 上有关于如何执行此操作的好文章: http://nuts-and-bolts-of-cakephp.com/2008/07/17/forcing-an-sql-join-in-cakephp/。此外,teknoid 将其变成了一种行为,您可以从 github 获取:http://nuts-and-bolts-of-cakephp.com/2009/09/26/habtamable-behavior/

** 这将拉按数据库中定义的顺序排列的列。因此,如果没有首先定义主键,它可能不会按预期应用 DISTINCT。您可能需要修改它以使用 array_diff_key 从 $this->Model->primaryKey 中过滤出主键。

The following was tested with Cake 1.3.

To start you probably want to, or already do, have the HABTM relationship defined on the models for all the other circumstances where this normally applies:

class Post extends AppModel {
    var $hasAndBelongsToMany = 'Tag';
}

class Tag extends AppModel {
    var $hasAndBelongsToMany = 'Post';
}

According to Cake's own documentation:[1]

In CakePHP some associations (belongsTo and hasOne) perform automatic joins to retrieve data, so you can issue queries to retrieve models
based on data in the related one.

But this is not the case with hasMany and hasAndBelongsToMany associations. Here is
where forcing joins comes to the rescue. You only have to define the necessary joins
to combine tables and get the desired results for your query.

Excluding empty HABTM results is one of these times. This same section of the Cake Book explains how to accomplish this, but I didn't find it overly obvious from reading the text that the result achieves this. In the example in the Cake Book, they use the\ join path Book -> BooksTag -> Tags, instead of our Tag -> PostsTag -> Posts. For our example, we'd set it up as follows from in the TagController:

$options['joins'] = array(
    array(
        'table'      => 'posts_tags',
        'alias'      => 'PostsTag',
        'type'       => 'INNER',
        'foreignKey' => false,
        'conditions' => 'PostsTag.tag_id = Tag.id'
    ),
    array(
        'table'      => 'posts',
        'alias'      => 'Post',
        'type'       => 'INNER',
        'foreignKey' => false,
        'conditions' => 'Post.id = PostsTag.post_id'
    )
);

$tagsWithPosts = $this->Tag->find('all', $options);

Make sure to set the foreignKey to false. This tells Cake that it should not attempt to figure out the join condition and instead use only the condition we supplied.

It's common that this will bring back duplicate rows due to the nature of the joins. To reduce the SQL returned use a DISTINCT on the fields as necessary. If you want all fields as is normally returned by find('all'), this adds the complication that you need to hard code each column. (Sure your table structure shouldn't change that often, but it could happen, or if you may just have a lot of columns). To grab all columns programmatically, add the following before the find method call:

$options['fields'] = array('DISTINCT Tag.'
                   . implode(', Tag.', array_keys($this->Tag->_schema)));
// **See note

It is important to note that the HABTM relationship runs AFTER the main select. Essentially, Cake gets the list of eligible Tags and then runs another round of SELECT statement(s) to get the associated Posts; you can see this from the SQL dump. The 'joins' we manually setup apply to the first select giving us the desired set of Tags. Then the built-in HABTM will run again to give us ALL associated Posts to those tags. We won't have any tags which have no Posts, our goal, but we may get posts associated with the tag that are not part of any of our initial 'conditions', if they were added.

For example, adding the following condition:

$options['conditions'] = 'Post.id = 1';

Will yield the following result:

Array (
    [0] => Array (
            [Tag] => Array (
                      [id] => 1
                      [name] => 'Tag1' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 1
                              [title] => 'Post1' )
                      [1] => Array (
                              [id] => 4
                              [title] => 'Post4' ) )
    )
)

Based on the sample data in the question, only Tag1 was associated with our 'conditions' statement. So this was the only result returned by the 'joins'. However, since the HABTM ran after this, it grabbed all Posts (Post1 and Post4) that were associated with Tag1.

This method of using explicit joins to get the desired initial data set is also discussed in Quick Tip - Doing Ad-hoc Joins in Model::find(). This article also shows how to generalize the technique and add it to the AppModel extending find().

If we really only wanted to see Post1 as well, we would need to add a 'contain'[2] option clause:

$this->Tag->Behaviors->attach('Containable');
$options['contain'] = 'Post.id = 1';

Giving the result:

Array (
    [0] => Array (
            [Tag] => Array (
                      [id] => 1
                      [name] => 'Tag1' )
            [Post] => Array (
                      [0] => Array (
                              [id] => 1
                              [title] => 'Post1' ) )
    )
)

Instead of using Containable you could use bindModel to redefine the HABTM relationship with this instance of find(). In the bindModel you would add the desired Post condition:

$this->Tag->bindModel(array(
    'hasAndBelongsToMany' => array(
        'Post' => array('conditions' => 'Post.id = 1'))
    )
);

I feel that for beginners trying to wrap their head around the automagic abilities of cake, making the explicit joins is easier to see and understand (I know it was for me). Another valid and arguably more 'Cake' way to do this would be to use unbindModel and bindModel exclusively. Teknoid over at http://nuts-and-bolts-of-cakephp.com has a good write up on how to do this: http://nuts-and-bolts-of-cakephp.com/2008/07/17/forcing-an-sql-join-in-cakephp/. Additionally, teknoid made this into a Behavior which you can grab from github: http://nuts-and-bolts-of-cakephp.com/2009/09/26/habtamable-behavior/

** This will pull the columns in the order defined in the database. So if the primary key is not defined first it may not apply DISTINCT as expected. You may need to modify this to use array_diff_key to filter out the primary key from $this->Model->primaryKey.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文