codeigniter 联接多个表并从第三个表中获取总行数

尝试加入4 - 5表在一次作为想抓住多个数据，这是存储在5表在第5表我试图计数的球员总数已加入比赛，这是工作正常，但我在这里面临的主要问题是，当没有数据在主表中，它仍然返回我1行与所有字段为空，除了总球员显示0，因为它显示不返回任何行，有人能帮助他们吗？下面是我查询

$getTournamentData = $this->db->select('tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players');
    $getTournamentData = $this->db->join('categories', 'categories.id = tournament.category_id');
    $getTournamentData = $this->db->join('games', 'games.game_id = tournament.game_id');
    $getTournamentData = $this->db->join('tournament_meta', 'tournament_meta.post_id = tournament.id');
    $getTournamentData = $this->db->join('tournament_players', 'tournament_players.tournamentID = tournament.id', 'left');

    $dataCond['created_by'] = $this->session->userdata('user_id');

    if($id != null) {
        $dataCond['tournament.id'] = $id;
    }

    $getTournamentData = $this->db->where($dataCond);
    $getTournamentData = $this->db->get('tournament');

因此返回total_player显示0，其余全部为空，因为表中未插入任何数据，但显示它不应从数据库返回任何数据

您在SELECT子句中混合了聚合函数（count()）和普通列名，这会产生意外的结果。请参阅：Why can't you mix Aggregate values and Non-Aggregate values in a single SELECT?
您可以新增GROUP BY子句，其中包含SELECT子句中的所有数据行名称，但包含count()的数据行名称除外。请确定在GROUP BY中完整输入tournament.*的所有数据行名称，因此请改用tournament.id, tournament.category_id, tournament.game_id etc：

SELECT tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players
FROM tournament
JOIN categories ON categories.id = tournament.category_id
JOIN games ON games.game_id = tournament.game_id
JOIN tournament_meta ON tournament_meta.post_id = tournament.id
JOIN tournament_players ON tournament_players.tournamentID = tournament.id
GROUP BY
tournament.id, tournament.category_id, tournament.game_id,
-- add other tournament colums here --
tournament_meta.meta_title, tournament_meta.meta_description, categories.title, categories.slug, games.game_name, games.slug

在CodeIgniter（3）中，这将转换为：

$this->db->select('tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players');
$this->db->from('tournament');
$this->db->join('categories', 'categories.id = tournament.category_id');
$this->db->join('games', 'games.game_id = tournament.game_id');
$this->db->join('tournament_meta', 'tournament_meta.post_id = tournament.id');
$this->db->join('tournament_players', 'tournament_players.tournamentID = tournament.id');
$this->db->group_by('tournament.id, tournament.category_id, tournament.game_id,
/* add other tournament columns here */
tournament_meta.meta_title, tournament_meta.meta_description, categories.title, categories.slug, games.game_name, games.slug');

或者，您也可以使用子选择，在这种情况下，您可以删除与tournament_players表的连接：

SELECT tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, (
    SELECT count(id)
    FROM tournament_players
    WHERE tournament_players.tournamentID = tournament.id) AS total_players
FROM tournament
JOIN categories ON categories.id = tournament.category_id
JOIN games ON games.game_id = tournament.game_id
JOIN tournament_meta ON tournament_meta.post_id = tournament.id

在CodeIgniter中与$this->db->query()一起使用。
显然我还没有测试这些查询，所以可能会有错误。希望这能帮助你开始。

codeigniter 联接多个表并从第三个表中获取总行数

1条答案

相关问题

热门标签

最新问答