我如何在 CakePHP 中过滤深层关联 [英] How do I filter deep associations in CakePHP

问题描述

我有以下表格:活页夹、文档、用户、docs_users.Doc 属于 Binder，Doc hasAndBelongsToMany 用户.

I have the following tables: binders, docs, users, docs_users. Doc belongsTo Binder, Doc hasAndBelongsToMany User.

我想为当前登录的用户获取活页夹及其相关文档(docs_users 表中的相关 user_id).

I want to get binders and their associated docs for the user that is currently logged in (the associated user_id in the docs_users table).

我已经尝试了 Containable 和 find('all') with joins、conditions 等，但我不知道如何删除来自未在 docs_users 表中关联的用户的文档.

I have tried Containable and find('all') with joins, conditions, etc. but I can't figure out how to remove the Docs that are from Users who are not associated in the docs_users table.

此代码不起作用:

$binders = $this->Binder->find( 
        'all',                  
        array( 
            'joins' => array(
                array( 
                    'table' => 'binders_users', 
                    'alias' => 'BindersUser', 
                    'type' => 'inner', 
                    'foreignKey' => false, 
                    'conditions'=> array(
                        'BindersUser.binder_id = Binder.id',
                        'BindersUser.user_id = ' . $this->Auth->user('id')
                    )
                ),
                array( 
                    'table' => 'docs', 
                    'alias' => 'Doc', 
                    'type' => 'left', 
                    'foreignKey' => false, 
                    'conditions'=> array(
                        'Doc.binder_id = Binder.id',
                    )
                ),                          
                array( 
                    'table' => 'docs_users', 
                    'alias' => 'DocsUser', 
                    'type' => 'left', 
                    'foreignKey' => false, 
                    'conditions'=> array(
                        'DocsUser.doc_id = Doc.id',
                        'DocsUser.user_id = ' . $this->Auth->user('id')
                    )
                )           
            ),
            'recursive'=>0
        )
    );
$this->set('binders', $binders);

这也不是:

$this->Binder->recursive = 2;
$this->Binder->Behaviors->attach('Containable');
$this->Binder->contain(array(
    'Branch',
    'Doc' => array(                     
        'User' => array(
            'DocsUser' => array(
                'conditions' => array('id = "17"')
            )
        )
    )
));
$binders = $this->Binder->find('all');

经验丰富的专业人士的任何帮助都会很棒！谢谢！

Any help from you seasoned pros would be great! Thanks!

替代/简化的解决方案?

如果我只想获取用户具有权限的活页夹，这有效.简短而甜蜜.但是，它仍然会发送所有相关的文档，这不是我想要的行为.它只需要传递用户有权访问的文档(如前所述).

This works if I just want to get binders to which users have permissions. Short and sweet. However, it will still send ALL associated docs through, which is NOT the behavior I want. It needs to only pass on the docs to which the user has permissions (as described previously).

$binders = $this->Binder->find( 
    'all',                  
    array( 
        'joins' => array(
            array( 
                'table' => 'binders_users', 
                'alias' => 'BindersUser', 
                'type' => 'inner', 
                'foreignKey' => false, 
                'conditions'=> array(
                    'BindersUser.binder_id = Binder.id',
                    'BindersUser.user_id = ' . $this->Auth->user('id')
                )
            )
        )
    )
);

推荐答案

以下是我根据收到的所有重要反馈提出的最终解决方案.我认为这是一个优雅的解决方案，可以在任何需要深度关联的场景中重复使用.

Here's the final solution I came up with based on all of the great feedback I got. I think this is an elegant solution that can be reused in any scenario where deep associations are required.

在 binder_controller 中，我解除了 Doc 模型的绑定，然后使用 finderQuery 将其绑定回以仅选择用户有权查看的 Docs.然后在加入 binders_users 表中，仅选择用户有权访问的 binders.

In the binder_controller I unbound the Doc model, and bound it back using the finderQuery to select only the Docs that a user has permission to see. Then in joined the binders_users table selecting only the binders that users have permissions to.

感谢大家的帮助！

$this->Binder->unbindModel(array('hasMany' => array('Doc')));
$this->Binder->bindModel(
    array('hasMany' => array(
            'Doc' => array(
                'className' => 'Doc',
                'foreignKey' => 'binder_id',
                'dependent' => false,
                'finderQuery' => 'SELECT Doc.* FROM docs AS Doc INNER JOIN docs_users AS DocsUser ON DocsUser.doc_id = Doc.id AND DocsUser.user_id = ' . $this->Auth->user('id')
            )
        )
    )
);

$binders = $this->Binder->find( 
    'all',                  
    array( 
        'joins' => array(
            array( 
                'table' => 'binders_users', 
                'alias' => 'BindersUser', 
                'type' => 'inner', 
                'foreignKey' => false, 
                'conditions'=> array(
                    'BindersUser.binder_id = Binder.id',
                    'BindersUser.user_id = ' . $this->Auth->user('id')
                )
            )               
        )
    )
);

更多关于绑定/解除绑定模型

这篇关于我如何在 CakePHP 中过滤深层关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！