CakePHP 3:包含具有COUNT()的深度关联模型 [英] CakePHP 3: Contain Deep Associated Model with COUNT()

问题描述

我有以下相关的表对象:

I have the following related table objects:

SettingsTable 属于 SettingsHeadersTable

SettingsHeadersTable 有很多 SettingsTable

SettingsOptionsTable 属于 SettingsTable

SettingsTable 有很多 SettingsOptionsTable

例如:

• 有一个标题为常规站点设置"的标题.
• 该标题下有一个名为离线状态"的设置.
• 在该设置下，在线"，离线"有3个选项 (受限制)"上的离线(不受限制)"

• There is a header titled "General Site Settings".
• There is a setting under that header titled "Offline Status".
• There are 3 options under that setting for "Online", "Offline (restricted)" on "Offline (unrestricted)"

我有一个控制器，我想找到所有标题，包含这些标题下的所有设置，并进一步包含那些设置下的所有选项.然后，在我看来，我可以循环浏览每个设置，并按标题将其分为几部分，并为每个设置显示相应的选项，以便管理员进行更新.

I have a controller that I would like to find all of the headers, contain all of the settings under those headers and further contain all of the options under those settings. Then in my view I can loop through each setting, divided into sections by header, and display the corresponding options for each setting for the admin to update.

下面，我的原始查询准确显示了上面的描述，但是，即使标头具有0个可见设置，它也显示了标头.我想隐藏具有0个可见设置的标题.

Below, my original query shows exactly what is describe above, however, it is showing headers even if they have 0 visible settings. I want to hide the headers that have 0 visible settings.

    $settings_headers = $this->Settings->SettingsHeaders->find()
    ->order([
        'SettingsHeaders.number' => 'ASC'
    ])
    ->where([
        'SettingsHeaders.module_id' => 1
    ])
    ->contain([
        'Settings' => function ($q) {
           return $q
                ->order(['Settings.number' => 'ASC'])
                ->where([
                    'Settings.hide' => 0
                ])
                ->contain([
                    'SettingsOptions' => function ($q) {
                       return $q
                             ->order(['SettingsOptions.number' => 'ASC']);
                    }
                ]);
        }
    ])

    ->toArray();

我进行了一些调整，仅显示可见设置的标题以及其对应的设置，但未显示选项.我仍然对包含与匹配感到困惑.我不确定我编写的代码是否多余，我一直在尝试遵循我在网上看到的示例.

I made some tweaks and got to the headers with only visible settings to show along with their corresponding settings, but the options are not showing. I'm still very confused on contain vs matching. I'm not sure if the code I've written is redundant, I've been trying to follow example I've seen online.

    $settings_headers = $this->Settings->SettingsHeaders->find()
    ->order([
        'SettingsHeaders.number' => 'ASC'
    ])
    ->where([
        'SettingsHeaders.module_id' => 1
    ])
    ->select([
        'SettingsHeaders.id',
        'SettingsHeaders.title',
        'SettingsHeaders.number',
        'SettingsHeaders.module_id',
        'settings_count' => 'COUNT(Settings.id)',                               
    ])
    ->select($this->Settings)
    ->group('SettingsHeaders.id')
    ->contain([
        'Settings' => function ($q) {
           return $q
                ->order(['Settings.number' => 'ASC'])
                ->where([
                    'Settings.hide' => 0 // ONLY SHOW VISIBLE SETTINGS
                ])
                ->contain([
                    'SettingsOptions' => function ($q) {
                       return $q
                             ->order(['SettingsOptions.number' => 'ASC']);
                    }
                ]);
        }
    ])
    ->matching('Settings', function($q) {
        return $q->where([
            'Settings.hide' => 0 // ONLY COUNT THE VISIBLE SETTINGS
        ]);
    })
    ->toArray();

我不太确定如何将它们放在一起(仅显示带有可见选项的标题，并且还包含Settings和SettingsOptions中的所有数据)

I'm not quite sure how to put it all together (only showing the headers with visible options, and also containing all of the data in both Settings and SettingsOptions)

推荐答案

实际上，我只是想出了这一点.我必须从第二个示例中删除以下行:

Actually, I just figured this out. I had to remove the following line from my second example:

->select($this->Settings)

这篇关于CakePHP 3:包含具有COUNT()的深度关联模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！