Subquery error when using 3rd Normal form - 500 Operand should contain 1 column(s)

Question

I'm fighting to get the following structure to work. I have three tables: players, player_teams, teams

I used the conclusion I've found here for one of my questions: Join one to many with helper table JDatabase But I've identified now an issue with this solution, when setting a "limit". Because this solution results in multiple items returned when a player is listed in multiple teams.

players_teams holds the player_id, the team_id aswell as some other information like from / till position and player number. So when a player plays on multiple positions for one team it is ok to have multiple entries in the player_teams table.

So to solve this problem i have now tried to use a SubQuery:

// Subquery all teams for a player and use them as array in the player object as "teams"
        $subQuery = $db->getQuery(true);
        $subQuery->select('t.title as team_title, t.id as team_id, pt.since as team_since, pt.until as team_until, t.ordering as team_ordering')
            ->from($db->quoteName('#__footballmanager_players_teams', 'pt'))
            ->join(
                'LEFT',
                $db->quoteName('#__footballmanager_teams', 't') . ' ON ' . $db->quoteName('t.id') . ' = ' . $db->quoteName('pt.team_id')
            )
            ->where($db->quoteName('pt.player_id') . ' = ' . $db->quoteName('a.id'));
        $query->select('(' . $subQuery . ') as teams');
...

But this results in an error 500 telling me that 500 Operand should contain 1 column(s)

How can i solve this issue? It's the first time I'm playing around with subqueries.

Answer 1

Okey took some time to recherche but have my solution now:

...
if (is_numeric($filterTeamId))
        {
            // Select teamIds using a nested query
            $subQueryFilterTeam = $db->getQuery(true);
            $subQueryFilterTeam->select('GROUP_CONCAT(DISTINCT ' . $db->quoteName('ptf.team_id') . ' ORDER BY ' . $db->quoteName('ptf.ordering') . ' SEPARATOR ",") AS teamIds')
                ->from($db->quoteName('#__footballmanager_players_teams', 'ptf'))
                ->where($db->quoteName('ptf.player_id') . ' = ' . $db->quoteName('a.id'));

            // filter for active Only
            $onlyActive = $this->getState('filter.only_active');
            if ($onlyActive)
            {
                $subQueryFilterTeam->where('(' . $db->quoteName('ptf.since') . ' IS NULL OR ' . $db->quoteName('ptf.since') . ' <= NOW())');
                $subQueryFilterTeam->where('(' . $db->quoteName('ptf.until') . ' IS NULL OR ' . $db->quoteName('ptf.until') . ' >= NOW())');
            }

            // Modify the main query to include the nested query in the WHERE clause
            $query->where($db->quote($filterTeamId) . ' IN (' . $subQueryFilterTeam . ')');
        }
...