CakePHP find方法用JOIN [英] CakePHP find method with JOIN

问题描述

您好，

我需要使用CakePHP find 方法执行以下查询：

  SELECT * 
 FROM`messages` 
 INNER JOIN users ON messages.from = users.id 
 WHERE messages.to = 4 
 ORDER BY messages.datetime DESC 
  

基本上我有： p>

• 消息表含有消息


• 
  
• 用户 $ b

，并希望在一个查询中从两个表中检索信息。 users.id 字段与 messages.from 字段相同，这就是连接所在。 / p>

我在 MessagesController 中这样做，所以它需要是这样的：

  $ this-> Message-> find（）; 
  

感谢

解决方案

有两种主要方法可以做到这一点。其中一个是标准的CakePHP方法，另一个是使用自定义的连接。

值得指出的是，这个建议是为CakePHP 2.x而不是3。 x。

CakePHP方式

您将与您的User模型和Messages Model建立关系，可控行为：

  class User extends AppModel {
 public $ actsAs = array（'Containable'）; 
 public $ hasMany = array（'Message'）; 
} 
 
类消息extend AppModel {
 public $ actsAs = array（'Containable'）; 
 public $ belongsTo = array（'User'）; 
} 
  

您需要更改 messages.from 列为 messages.user_id ，以便蛋糕可以自动关联您的记录。

然后可以从消息控制器执行此操作：

  $ this-> Message-> find （
'contains'=> array（'User'）
'conditions'=> array（
'Message.to'=> 4 
），
'order'=>'Message.datetime DESC'
））; 
  

（其他）CakePHP方式

我建议使用第一种方法，因为它会节省大量的时间和工作。第一种方法也建立一个关系的基础，可以用于任何数量的其他查找调用和条件，除了你现在需要的。但是，cakePHP不支持定义自己的联接的语法。它会这样做，从 MessagesController ：

  $ this - > Message-> find（'all'，array（
'joins'=&array; array（
 array（
'table'=>'users'，
'alias'=>'UserJoin'，
'type'=>'INNER'，
'conditions'=> array（
'UserJoin.id = Message.from' 
）
）
），
'conditions'=> array（
'Message.to'=> 4 
），
'fields'=> array（'UserJoin。*'，'Message。*'），
'order'=>'Message.datetime DESC'
） 
  

注意，我已离开字段名称 messages.from



使用与同一模型的两个关系

这里是如何使用两个关系到同一个模型来做第一个例子：

  class User extends AppModel {
 public $ actsAs = array（'Containable'）; 
 public $ hasMany = array（
'MessagesSent'=> array（
'className'=>'Message'，
'foreignKey'=>'from'
）
）; 
 public $ belongsTo = array（
'MessagesReceived'=> array（
'className'=>'Message'，
'foreignKey'=>'to'
）
）; 
} 
 
类消息extend AppModel {
 public $ actsAs = array（'Containable'）; 
 public $ belongsTo = array（
'UserFrom'=> array（
'className'=>'User'，
'foreignKey'=>'from'
）
）; 
 public $ hasMany = array（
'UserTo'=> array（
'className'=>'User'，
'foreignKey'=>'to'
）
）; 
} 
  

现在您可以像这样进行查找：

  $ this-> Message-> find（'all'，array（
'contains'=> array（'UserFrom' ）
'conditions'=> array（
'Message.to'=> 4 
），
'order'=>'Message.datetime DESC'
））; 
  

Hi,

I need to do the following query using the CakePHP find method:

SELECT *
FROM `messages`
INNER JOIN users ON messages.from = users.id
WHERE messages.to = 4
ORDER BY messages.datetime DESC

Basically I have:

• messages table with a Message model
• users table with User model

and want to retrieve information from both tables in one query. The users.id field is the same as the messages.from field, so that's what the join is on.

I am doing it in my MessagesController so it would need to be something like:

$this->Message->find();

Thanks

解决方案

There are two main ways that you can do this. One of them is the standard CakePHP way, and the other is using a custom join.

It's worth pointing out that this advice is for CakePHP 2.x, not 3.x.

The CakePHP Way

You would create a relationship with your User model and Messages Model, and use the containable behavior:

class User extends AppModel {
    public $actsAs = array('Containable');
    public $hasMany = array('Message');
}

class Message extends AppModel {
    public $actsAs = array('Containable');
    public $belongsTo = array('User');
}

You need to change the messages.from column to be messages.user_id so that cake can automagically associate the records for you.

Then you can do this from the messages controller:

$this->Message->find('all', array(
    'contain' => array('User')
    'conditions' => array(
        'Message.to' => 4
    ),
    'order' => 'Message.datetime DESC'
));

The (other) CakePHP way

I recommend using the first method, because it will save you a lot of time and work. The first method also does the groundwork of setting up a relationship which can be used for any number of other find calls and conditions besides the one you need now. However, cakePHP does support a syntax for defining your own joins. It would be done like this, from the MessagesController:

$this->Message->find('all', array(
    'joins' => array(
        array(
            'table' => 'users',
            'alias' => 'UserJoin',
            'type' => 'INNER',
            'conditions' => array(
                'UserJoin.id = Message.from'
            )
        )
    ),
    'conditions' => array(
        'Message.to' => 4
    ),
    'fields' => array('UserJoin.*', 'Message.*'),
    'order' => 'Message.datetime DESC'
));

Note, I've left the field name messages.from the same as your current table in this example.

Using two relationships to the same model

Here is how you can do the first example using two relationships to the same model:

class User extends AppModel {
    public $actsAs = array('Containable');
    public $hasMany = array(
        'MessagesSent' => array(
            'className'  => 'Message',
            'foreignKey' => 'from'
         )
    );
    public $belongsTo = array(
        'MessagesReceived' => array(
            'className'  => 'Message',
            'foreignKey' => 'to'
         )
    );
}

class Message extends AppModel {
    public $actsAs = array('Containable');
    public $belongsTo = array(
        'UserFrom' => array(
            'className'  => 'User',
            'foreignKey' => 'from'
        )
    );
    public $hasMany = array(
        'UserTo' => array(
            'className'  => 'User',
            'foreignKey' => 'to'
        )
    );
}

Now you can do your find call like this:

$this->Message->find('all', array(
    'contain' => array('UserFrom')
    'conditions' => array(
        'Message.to' => 4
    ),
    'order' => 'Message.datetime DESC'
));

这篇关于CakePHP find方法用JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！