php CakePHP 使用 JOIN 查找方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5079908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CakePHP find method with JOIN
提问by geoffs3310
Hi,
你好，
I need to do the following query using the CakePHP findmethod:
我需要使用 CakePHPfind方法执行以下查询：
SELECT *
FROM `messages`
INNER JOIN users ON messages.from = users.id
WHERE messages.to = 4
ORDER BY messages.datetime DESC
Basically I have:
基本上我有：
messagestable with aMessagemodeluserstable withUsermodel
messages带Message模型的桌子users带User模型的表
and want to retrieve information from both tables in one query. The users.idfield is the same as the messages.fromfield, so that's what the join is on.
并希望在一个查询中从两个表中检索信息。该users.id字段与该messages.from字段相同，因此这就是连接所在的位置。
I am doing it in my MessagesControllerso it would need to be something like:
我正在做我的MessagesController所以它需要是这样的：
$this->Message->find();
Thanks
谢谢
回答by Stephen
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.
有两种主要方法可以做到这一点。其中一种是标准的 CakePHP 方式，另一种是使用自定义连接。
It's worth pointing out that this advice is for CakePHP 2.x, not 3.x.
值得指出的是，这个建议是针对 CakePHP 2.x，而不是 3.x。
The CakePHP Way
CakePHP 方式
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.fromcolumn to be messages.user_idso that cake can automagically associate the records for you.
您需要将messages.from列更改为，messages.user_id以便 cake 可以自动为您关联记录。
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
（其他）CakePHP 方式
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:
我建议使用第一种方法，因为它会为您节省大量时间和工作。第一种方法还为建立关系奠定了基础，该关系可用于除您现在需要的之外的任意数量的其他查找调用和条件。但是，cakePHP 确实支持定义您自己的连接的语法。它会像这样完成，从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.fromthe same as your current table in this example.
请注意，messages.from在此示例中，我保留了与当前表相同的字段名称。
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:
现在你可以像这样进行 find 调用：
$this->Message->find('all', array(
'contain' => array('UserFrom')
'conditions' => array(
'Message.to' => 4
),
'order' => 'Message.datetime DESC'
));
回答by Sergio
Otro example, custom Data Pagination for JOIN
Otro 示例，用于 JOIN 的自定义数据分页
CODE in Controller CakePHP 2.6 is OK:
控制器 CakePHP 2.6 中的代码是可以的：
$this->SenasaPedidosFacturadosSds->recursive = -1;
// Filtro
$where = array(
'joins' => array(
array(
'table' => 'usuarios',
'alias' => 'Usuarios',
'type' => 'INNER',
'conditions' => array(
'Usuarios.usuario_id = SenasaPedidosFacturadosSds.usuarios_id'
)
),
array(
'table' => 'senasa_pedidos',
'alias' => 'SenasaPedidos',
'type' => 'INNER',
'conditions' => array(
'SenasaPedidos.id = SenasaPedidosFacturadosSds.senasa_pedidos_id'
)
),
array(
'table' => 'clientes',
'alias' => 'Clientes',
'type' => 'INNER',
'conditions' => array(
'Clientes.id_cliente = SenasaPedidos.clientes_id'
)
),
),
'fields'=>array(
'SenasaPedidosFacturadosSds.*',
'Usuarios.usuario_id',
'Usuarios.apellido_nombre',
'Usuarios.senasa_establecimientos_id',
'Clientes.id_cliente',
'Clientes.consolida_doc_sanitaria',
'Clientes.requiere_senasa',
'Clientes.razon_social',
'SenasaPedidos.id',
'SenasaPedidos.domicilio_entrega',
'SenasaPedidos.sds',
'SenasaPedidos.pt_ptr'
),
'conditions'=>array(
'Clientes.requiere_senasa'=>1
),
'order' => 'SenasaPedidosFacturadosSds.created DESC',
'limit'=>100
);
$this->paginate = $where;
// Get datos
$data = $this->Paginator->paginate();
exit(debug($data));
OR Example 2, NOT active conditions:
或示例 2，非活动条件：
$this->SenasaPedidosFacturadosSds->recursive = -1;
// Filtro
$where = array(
'joins' => array(
array(
'table' => 'usuarios',
'alias' => 'Usuarios',
'type' => 'INNER',
'conditions' => array(
'Usuarios.usuario_id = SenasaPedidosFacturadosSds.usuarios_id'
)
),
array(
'table' => 'senasa_pedidos',
'alias' => 'SenasaPedidos',
'type' => 'INNER',
'conditions' => array(
'SenasaPedidos.id = SenasaPedidosFacturadosSds.senasa_pedidos_id'
)
),
array(
'table' => 'clientes',
'alias' => 'Clientes',
'type' => 'INNER',
'conditions' => array(
'Clientes.id_cliente = SenasaPedidos.clientes_id',
'Clientes.requiere_senasa = 1'
)
),
),
'fields'=>array(
'SenasaPedidosFacturadosSds.*',
'Usuarios.usuario_id',
'Usuarios.apellido_nombre',
'Usuarios.senasa_establecimientos_id',
'Clientes.id_cliente',
'Clientes.consolida_doc_sanitaria',
'Clientes.requiere_senasa',
'Clientes.razon_social',
'SenasaPedidos.id',
'SenasaPedidos.domicilio_entrega',
'SenasaPedidos.sds',
'SenasaPedidos.pt_ptr'
),
//'conditions'=>array(
// 'Clientes.requiere_senasa'=>1
//),
'order' => 'SenasaPedidosFacturadosSds.created DESC',
'limit'=>100
);
$this->paginate = $where;
// Get datos
$data = $this->Paginator->paginate();
exit(debug($data));
回答by Atanu Dolui
$services = $this->Service->find('all', array(
'limit' =>4,
'fields' => array('Service.*','ServiceImage.*'),
'joins' => array(
array(
'table' => 'services_images',
'alias' => 'ServiceImage',
'type' => 'INNER',
'conditions' => array(
'ServiceImage.service_id' =>'Service.id'
)
),
),
)
);
It goges to array is null.
它转到数组为空。
