如何对数组中的特定字段进行分组? [英] How to group a specific field in array?
问题描述
我需要获得一个团队赢得的trophies，每个奖杯都是在特定赛季的比赛中获得的，但是一个团队可以在不同季节的同一场比赛中赢得奖杯.
I need to get the trophies won by a team, each trophy is achieved in a competition into a specific season, but a team can win a trophy for the same competition per different seasons.
我写了一个查询，从数据库中提取奖杯:
I wrote a query that extract the trophies from the database:
$sql = $this->db->prepare("SELECT t.*, s.*, c.*,
t.team_id as team_id,
s.id as season_id,
s.name as season_name,
c.id as competition_id,
c.name as competition_name
FROM team_trophies t
INNER JOIN competition_seasons s ON s.id = t.season_id
INNER JOIN competition c ON c.id = s.competition_id
WHERE team_id = :team_id
");
基本上，我从表team_trophies中选择了特定团队的所有trophies，并加入了competition_seasons以检索奖杯的赛季细节，与competition表相同.
essentially I selected all the trophies for a specific team from the table team_trophies and joined the competition_seasons for retrieve the season details of the trophy, the same for competition table.
这行得通，我得到:
[
{
"team_id": "23291",
"season_id": "2",
"position": "Winner",
"wins": "4",
"id": "1093",
"competition_id": "1093",
"name": "Premier League",
"update_at": "2018-06-04 12:12:30",
"country_id": "1",
"category": "1",
"season_name": "2017",
"competition_name": "Premier League"
},
{
"team_id": "23291",
"season_id": "3",
"position": "Runner-up",
"wins": "1",
"id": "1093",
"competition_id": "1093",
"name": "Premier League",
"update_at": "2018-06-04 12:14:39",
"country_id": "1",
"category": "1",
"season_name": "2015",
"competition_name": "Premier League"
}
]
但是我会返回这样的结果:
but I would return a result like this:
[
{
"team_id": "23291",
"position": "Winner",
"wins": "4",
"id": "1093",
"competition_id": "1093",
"name": "Premier League",
"update_at": "2018-06-04 12:12:30",
"country_id": "1",
"category": "1",
"seasons": [
["season_name":"2017", "season_id":"2"],
["season_name":"2015", "season_id":"3"],
]
"competition_name": "Premier League"
}
]
如您所见，结果中有一行包含该奖杯的seasons作为array，这更具可读性，并且避免了重复.
as you can see I have one row in the result that contains the seasons of that trophy as an array, this is more readable and avoid redundancy.
是否可以使用sql做到这一点?还是我需要解决php?
Is possible achieve this using sql? Or I need to workaround with php?
谢谢.
更新-表格结构
CREATE TABLE IF NOT EXISTS `swp`.`competition` (
`id` INT NOT NULL,
`name` VARCHAR(255) NULL,
PRIMARY KEY (`id`),
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `swp`.`competition_seasons` (
`id` INT NOT NULL AUTO_INCREMENT,
`competition_id` INT NOT NULL,
`season_id` INT NULL,
`name` VARCHAR(45) NOT NULL,
`update_at` DATETIME NULL,
PRIMARY KEY (`id`),
INDEX `FK_competition_competition_seasons_competition_id_idx` (`competition_id` ASC),
CONSTRAINT `FK_competition_competition_seasons_competition_id`
FOREIGN KEY (`competition_id`)
REFERENCES `swp`.`competition` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `swp`.`team_trophies` (
`team_id` INT NOT NULL,
`season_id` INT NOT NULL,
`position` VARCHAR(255) NOT NULL,
`wins` INT NOT NULL,
INDEX `FK_team_team_trophies_team_id_idx` (`team_id` ASC),
INDEX `FK_season_team_trophies_season_id_idx` (`season_id` ASC),
CONSTRAINT `FK_team_team_trophies_team_id`
FOREIGN KEY (`team_id`)
REFERENCES `swp`.`team` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `FK_season_team_trophies_season_id`
FOREIGN KEY (`season_id`)
REFERENCES `swp`.`competition_seasons` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Some INSERTs
INSERT INTO `team_trophies` (`team_id`, `season_id`, `position`, `wins` VALUES (23291, 2, 'Winner', 4), (23291, 3, 'Runner-up', 1);
INSERT INTO `competition` (`id`, `country_id`, `name`, `category`) VALUES (1093, 1, 'Premier League', 1);
INSERT INTO `competition_seasons` (`id`, `competition_id`, `season_id`,
`name`, `update_at`) VALUES
(1, 1093, 14963, '2018', '2018-06-04 12:10:28'),
(2, 1093, 13198, '2017', '2018-06-04 12:12:30');
推荐答案
由一位名叫Ghost的大师提供，这是一个将一维数组转换为多维数组的简单示例...
Courtesy of a guru called Ghost, here's a simple example of transforming a one dimensional array to a multi-dimensional one...
<?php
$my_array = array();
$my_array =
array
(
0 => array
(
id => '4',
project_id => '2289',
task => 'Drawing'
),
1 => array
(
id => '5',
project_id => '2289',
task => 'Surveying'
),
2 => array
(
id => '6',
project_id => '2289',
task => 'Meeting'
),
3 => array
(
id => '1',
project_id => '2282',
task => 'Folding'
),
4 => array
(
id => '2',
project_id => '2282',
task => 'Printing'
),
5 => array
(
id => '3',
project_id => '2282',
task => 'Cutting'
)
);
$new_array = array();
foreach ($my_array as $row) {
$new_array[$row['project_id']]['project_id'] = $row['project_id'];
$new_array[$row['project_id']]['task'][] = $row['task'];
}
$new_array = array_values($new_array); // reindex
// removes `$row['project_id']` on each group
print_r($new_array);
?>
输出:
Array
(
[0] => Array
(
[project_id] => 2289
[task] => Array
(
[0] => Drawing
[1] => Surveying
[2] => Meeting
)
)
[1] => Array
(
[project_id] => 2282
[task] => Array
(
[0] => Folding
[1] => Printing
[2] => Cutting
)
)
)
这篇关于如何对数组中的特定字段进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
