Summary of methods for querying related tables in Cakephp-php手册-php.cn

Summary of methods for querying related tables in Cakephp

黄舟

Release： 2016-12-20 09:35:07

Original

2071 people have browsed it

We can implement it in the following ways

{Query all the information containing the keyword keyword in the Note of the specified User}

1.SQL statement

SELECT * FROM Users AS User
LEFT JOIN Notes AS Note ON User.id = Note.user_id
WHERE
User.id = {$user_id}
AND
Note.subject LIKE &#39;%{keyword}%&#39;

Copy after login

Then we execute this SQL statement and use the query method of the model

$data = $this->User->query($sql);

Copy after login

2. Use the bindModel() and unbindModel() methods of the model

For instructions on these two methods, please refer to

http://api.cakephp.org/class/model

Our approach is

//重新绑定关联指定查询条件
 
$this->User->unbindModel(&#39;Note&#39;);
$this->User->bindModel(
    &#39;hasMany&#39; => array(
        &#39;Note&#39; => array(
            &#39;conditions&#39; => array(
                &#39;Note.subject LIKE&#39; => &#39;%&#39;.$keyword.&#39;%&#39;
            )
        )
    )
);
 
//指定主表条件获取数据
$data = $this->User->find(&#39;all&#39;,array(
    &#39;conditions&#39; => array(
        &#39;User.id&#39; => $user_id
    )
));
 
//或者
 
$data = $this->User->read(null,$user_id);

Copy after login

3. Using Cakephp’s core behavior (Behavior) Containable

We first create our own AppModel class and create the file /app/app_model.php

class AppModel extends Model {
 
    //加载核心行为
    var $actsAs = array(&#39;Containable&#39;);
 
}

Copy after login

Then we can query through such code in the controller

$this->User->contain(&#39;Note.subject LIKE&#39; => &#39;%&#39;.$keyword.&#39;%&#39;);
 
$data = $this->User->find(&#39;all&#39;,array(
    &#39;conditions&#39; => array(
        &#39;User.id&#39; => $user_id
    )
));

Copy after login

You can also write it directly into the find statement, similar to the following

$data = $this->User->find(&#39;all&#39;,array(
    &#39;conditions&#39; => array(
        &#39;User.id&#39; => $user_id
    ),
    &#39;contain&#39; => array(
        &#39;Note&#39; => array(
            &#39;conditions&#39; => array(
                &#39;Note.subject LIKE&#39; => &#39;%&#39;.$keyword.&#39;%&#39;
            )
        )
    )
));

Copy after login

Notes:

If you want to query {User.name or Note.subject all records containing the keyword keyword}

At this time, Cakephp's find method cannot be implemented This query must use the custom SQL statement introduced above, as follows:

SELECT *
FROM users AS User
LEFT JOIN notes AS Note ON User.id = Note.user_id
WHERE
User.name LIKE &#39;%keyword%&#39;
OR
Note.subject LIKE &#39;%keyword%&#39;

Copy after login

The above is a summary of the method of querying the association table in Cakephp. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!