php - 带左连接的 cakephp select 语句

Question

我有两个表，分别是Servers 和ApplicationsGroupsServers，其中server_id 是Servers.id 的外键。

我正在尝试编写一个查询以从服务器表中选择一个名为 server_name 的字段，然后左连接 ApplicationsGroupsServers 表中的所有数据。

$option = array(
    'join' => array(
            'table' => 'Servers',
            'alias' => 's',
            'type' => 'left',
            'conditions' => array( 's.id = ApplicationsGroupsServers.id' )
        )
);
$apps = $this->ApplicationsGroupsServers->find('all', array(
    "fields" => array('Servers.server_name'), ($option)
));
$this->set('applications', $apps);

但是，当我运行如下所示的查询时。我能够填充结果:

$apps = $this->ApplicationsGroupsServers->find('all', $option);

后端运行的SQL查询:

SELECT ApplicationsGroupsServers.id AS `ApplicationsGroupsServers__id`, 
    ApplicationsGroupsServers.application_id AS `ApplicationsGroupsServers__application_id`, 
    ApplicationsGroupsServers.group_id AS `ApplicationsGroupsServers__group_id`, 
    ApplicationsGroupsServers.server_id AS `ApplicationsGroupsServers__server_id` 
FROM applications_groups_servers ApplicationsGroupsServers 
     left JOIN Servers s ON s.id = ApplicationsGroupsServers.id

但是，我正在尝试运行这样的程序:

SELECT server_name
  FROM applications_groups_servers
       LEFT JOIN servers
          ON servers.id = applications_groups_servers.server_id
       LEFT JOIN groups ON groups.id = applications_groups_servers.group_id

Answer 1

您应该尝试在模型之间创建关系而不是使用join 查询，但如果您真的想使用join 查询:

$options = array(
    'fields' => array(
        'Server.name' // Server not Servers
    ),
    'join' => array(
        array(
            'table' => 'servers',
            'alias' => 'Server',
            'type' => 'LEFT',
            'conditions' => array(
                'Server.id = ApplicationsGroupsServers.server_id',
            )
        ),
        array(
            'table' => 'groups',
            'alias' => 'Group',
            'type' => 'LEFT',
            'conditions' => array(
                'Group.id = ApplicationsGroupsServers.group_id',
            )
        )
    )
) ;