Geek Answers Handbook

How to filter deep associations in CakePHP

I have the following tables: binders, docs, users, docs_users. Doc is owned by To Binder, Doc hasAndBelongsToMany User.

I want to get the bindings and related documents for the user who is currently logging in (the associated user_id in the docs_users table).

I tried Containable and find ('all') with joins, conditions, etc., but I cannot figure out how to delete Documents that belong to users who are not related in the docs_users table.

This code does NOT work:

$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);

And this is not so:

$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 experienced professionals will be great! Thank!

Alternative / simplified solutions?

, , . . , - , , . , ( ).

$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')
                )
            )
        )
    )
);
+5
4

, , , . , , , .

binder_controller Doc finderQuery, , . binders_users , .

!

$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')
                )
            )               
        )
    )
);

/

+4

Cake, ID :

'conditions' => array('id = "17"')

. DocsUser.id

... . .

0

Did you try to come from a user perspective?

$this->Binder->Doc->User->Behaviors->attach('Containable');
$this->Binder->Doc->User->contain(array('Doc'=>'Binder'));
$user = $this->Binder->Doc->User->find('all',array('conditions'=>'User.id'=>$user_id));

In any case, the association "DocsUser" must be detected.

In terms of binders, there may be

In your Binders MODEL add (please check the names of tables, keys and models in case I made a typo)

function getBindersByUserSql($user_id)
    {       
        $dbo = $this->getDataSource();
        $subQuery = $dbo->buildStatement(
            array(
                    'fields' => array('DISTINCT(Doc.binder_id)'),
                    'table' => "docs_users",                                       
                    'joins' => array(
                                array('table' => 'users',
                                    'alias' => 'User',
                                    'type' => 'INNER',
                                    'conditions' => array('DocsUser.user_id = User.id')
                                ),
                                array('table' => 'docs',
                                    'alias' => 'Doc',
                                    'type' => 'INNER',
                                    'conditions' => array('Doc.id = DocsUser.doc_id')
                                )
            ),
                    'alias'=>"DocsUser",                                            
                    'conditions' => array("User.id"=>$user_id),
                    'order' => null,
                    'group' => "Doc.binder_id"
                    ),
                    $this
                    );
        return $dbo->expression($subQuery);
    }

Then in your CONTROLLER inserts try

$this->Binder->Behaviors->attach('Containable');
$this->Binder->contain(array('Doc'));
$conditions = array();
$conditions = $this->Binder->getBindersByUserSql($this->Auth->user('id'));
$binders = $this->Binder->find('all',array('conditions'=>$conditions)));

Any good?

0
source

More articles:


All Articles