CakePHP - 使用JOIN进行多表联合查询

在 SELECT, UPDATE 和 DELETE 语句中可以使用 JOIN 来进行多表联合查询,JOIN按照功能分为以下三类:

  • INNER JOIN (内连接或等值连接):获取两个表中字段匹配关系的记录
  • LEFT JOIN (左连接):获取左表所有记录,即使右表没有对应匹配的记录
  • RIGHT JOIN (右连接):与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录

例如有以下三张数据表,它们之间没有关联关系,但都有city_code 字段:

  • 城市基础表 cities
  • 城市信息表 city_infos
  • 城市经济指标表 city_indicators

现需要从三张数据表中联合查询,原始SQL语句如下:

SELECT a.city_name, b.city_population, c.city_gdp
FROM cities a
INNER JOIN city_infos b
ON (
	a.city_code = b.city_code
	AND b.modified_year = '2020'
)
INNER JOIN city_indicators c
ON (
	a.city_code = c.city_code
	AND c.modified_year = '2020'
)
ORDER BY a.city_code ASC

在CakePHP的Controller类中实现以上联合查询,方式如下:

$this->Cities->find()
					->select([
						'Cities.city_name', 
						'CityInfos.city_population',
						'CityIndicators.city_gdp'
					])
					->innerJoin(
						[
							'CityInfos' => 'city_infos' //设置表的别名
						],
						[
							'Cities.city_code = CityInfos.city_code',
							'CityInfos.modified_year' => '2020'
						]
					)
					->innerJoin(
						[
							'CityIndicators' => 'city_indicators' //设置表的别名
						],
						[
							'Cities.city_code = CityIndicators.city_code',
							'CityIndicators.modified_year' => '2020'
						]
					)
					->order([
						'Cities.city_code' => 'ASC'
					])
					->map(function($row) { //构建数据格式
						return [
							'city_name' => $row->city_name,
							'city_population' => $row->CityInfos['city_population'],
							'city_gdp' => $row->CityIndicators['city_gdp']
						];
					})
					->toArray();

或者把所有要联合的表写在一起:

$this->Cities->find()
					->select([
						'Cities.city_name', 
						'CityInfos.city_population',
						'CityIndicators.city_gdp'
					])
					->join([
						'CityInfos' => [ //设置表的别名
							'table' => 'city_infos',
							'type' => 'INNER',
							'conditions' => [
								'CityInfos.city_code = Cities.city_code',
								'CityInfos.modified_year' => '2020'
							]
						],
						'CityIndicators' => [ //设置表的别名
							'table' => 'city_indicators',
							'type' => 'INNER',
							'conditions' => [
								'CityIndicators.city_code = Cities.city_code',
								'CityIndicators.modified_year' => '2020'
							]
						]
					])
					->order([
						'Cities.city_code' => 'ASC'
					])
					->map(function($row) { //构建数据格式
						return [
							'city_name' => $row->city_name,
							'city_population' => $row->CityInfos['city_population'],
							'city_gdp' => $row->CityIndicators['city_gdp']
						];
					})
					->toArray();
<div class="post-text" itemprop="text"> <p>I have two tables are <code>Servers</code> and <code>ApplicationsGroupsServers</code>, where <code>server_id</code> is foreign key to <code>Servers.id</code>.</p> <p>I am trying to write a query to select a field called server_name from servers table and left join all data from <code>ApplicationsGroupsServers</code> table.</p> <pre><code>$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); </code></pre> <p>But, when I run a query like shown below. I am able to populate results:</p> <pre><code>$apps = $this->ApplicationsGroupsServers->find('all', $option); </code></pre> <p>SQL Query in the back end that runs:</p> <pre><code>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 </code></pre> <p>However, I am trying to run something like this:</p> <pre><code>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 </code></pre> </div>
©️2020 CSDN 皮肤主题: 鲸 设计师:meimeiellie 返回首页