如何在 cakephp 中创建带有连接的查询 - How create a query with a join in cakephp

How create a query with a join in cakephp

本文关键字：连接查询创建 cakephp | 更新日期: 2023-09-27

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

SELECT * FROM fydee.clients_groups join clients on clients_groups.client_id = clients.id where clients.deleted = 0 and group_id = 7;

Client_groups.client_id 字段与 clients.idfield 相同，因此这就是联接所在的位置。如何在 cakephp 中创建它？

我试过：

$clients = $this->Groups->find('all', array(
            'joins' => array(
                array(
                    'table' => 'Clients',
                    'alias' => 'ClientsJoin',
                    'type' => 'INNER',
                    'conditions' => array(
                        'ClientsJoin.id = client.id'
                    )
                )
            ),
            'conditions' => array(
                'Group.id' => $_POST['group_id'],
                'Client.deleted' => 0
            )
        ));

看起来您正在尝试做这样的事情：-

$this->Group->find('all', [
    'joins' => [
        [
            'table' => 'clients',
            'alias' => 'Client',
            'type' => 'INNER',
            'conditions' => [
                'Client.id = Group.client_id'
            ]
        ]
    ],
    'conditions' => [
        'Group.id' => $_POST['group_id'],
        'Client.deleted' => 0
    ]
]);

在joins中声明alias时，它是要连接的表的别名。所以遵循 CakePHP 命名约定想要像 Client .然后，需要'Client.id = Group.client_id'加入条件。

您可能可以简单地使用contain（假设您的模型关联已正确设置）获得相同的结果，例如：-

$this->Group->find('all', [
    'contain' => ['Client'],
    'conditions' => [
        'Group.id' => $_POST['group_id'],
        'Client.deleted' => 0
    ]
]);

附带说明一下，您应该真正使用$this->request->data而不是$_POST蛋糕。