I have two tables are Servers
and ApplicationsGroupsServers
, where server_id
is foreign key to Servers.id
.
I am trying to write a query to select a field called server_name from servers table and left join all data from ApplicationsGroupsServers
table.
$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);
But, when I run a query like shown below. I am able to populate results:
$apps = $this->ApplicationsGroupsServers->find('all', $option);
SQL Query in the back end that runs:
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
However, I am trying to run something like this:
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