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'))
                $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.

1 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 . ')');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.