Join one to many with helper table JDatabase

Question

I'm currently using a two step solution to get all linked teams to a player (a player can be linked to multiple teams) that way for my backend list view: first regular SELECT from Players Then loop over the player items and get all linked teams as follow:

protected function getLinkedTeams($playerId){
        $teams = array();

        // Get Team ID's
        $db = $this->getDatabase();
        $query = $db->getQuery(true);
        $query->select('ct.team_id');
        $query->from($db->quoteName('#__component_players_teams', 'ct'));
        $query->where($db->quoteName('ct.player_id') . ' = ' . $db->quote($playerId));
        $db->setQuery($query);
        $teamIds = $db->loadColumn();

        // Get Team Names
        foreach ($teamIds as $teamId){
            if(!$teamId) continue;
            $query = $db->getQuery(true);
            $query->select('t.id, t.title');
            $query->from($db->quoteName('#__component_teams', 't'));
            $query->where($db->quoteName('t.id') . ' = ' . $db->quote($teamId));
            $query->where($db->quoteName('t.published') . ' = 1');
            $db->setQuery($query);
            $teams[] = $db->loadObject();
        }

        return $teams;

    }

the result will then be added to the player object. It's working like this. My Problem now is that i cannot add filters for "team names" because in the getItems (Players) methhod the Team Names are not present... Additionally the looping over the player objects is not that sexy... Can someone tell me if its possible to bring this additional query directly into the main (getItems) maybe as subquery? I've already tried a couple of things but I think my approach with this plyers_teams table (where just the player_id and the team_id got linked) is not the best solution if you want to use it like I intend to do...

Edit: I'm close i can feel it:

...
// Subquery to get all linked teams of each player by using the players_teams aswell as the teams table
        $playersTeamsLinkingQuery = $db->getQuery(true);
        $playersTeamsLinkingQuery->select('GROUP_CONCAT(DISTINCT pt.team_id SEPARATOR \', \')');
        $playersTeamsLinkingQuery->from($db->quoteName('#__component_players_teams', 'pt'));
        $playersTeamsLinkingQuery->where($db->quoteName('pt.player_id') . ' = ' . $db->quoteName('a.id'));

        $query->select('(' . $playersTeamsLinkingQuery . ') AS linked_team_ids');

// linked_team_ids == 1,2,3

        // SubQuery the team names based on the linked_team_ids
        $teamNamesQuery = $db->getQuery(true);
        $teamNamesQuery->select('GROUP_CONCAT(DISTINCT t.title SEPARATOR \',\')');
        $teamNamesQuery->from($db->quoteName('#__component_teams', 't'));
        $teamNamesQuery->where($db->quoteName('t.id') . ' IN (' . $playersTeamsLinkingQuery . ')');

        $query->select('(' . $teamNamesQuery . ') AS linked_team_names');

// linked_team_names == 'Name of Team with ID 1"

The first subquery gets me a comma separated list of id's for the teams table but the second subquery gives just one entry back...

Answer 1

So got it now:

In my select for the players:

...
$query->select('t.title as team_title, t.id as team_id')
            ->join(
                'LEFT',
                $db->quoteName('#__component_players_teams', 'pt') . ' ON ' . $db->quoteName('a.id') . ' = ' . $db->quoteName('pt.player_id')
            )
            ->join(
                'LEFT',
                $db->quoteName('#__component_teams', 't') . ' ON ' . $db->quoteName('t.id') . ' = ' . $db->quoteName('pt.team_id')
            );

...
// My Filter
$teamId = $this->getState('filter.team_id');
        if (is_numeric($teamId))
        {
            $query->where($db->quoteName('team_id') . ' = ' . (int) $teamId);
        }

And my getItems method i merge the "multiple" entries for each player:

public function getItems()
    {
        $items = parent::getItems();
        $processedResults = array();
        // Add Team Details
        foreach ($items as &$item)
        {
            // Required to prevent multiple entries for the same player
            if(!isset($processedResults[$item->id])){
                $processedResults[$item->id] = $item;
                $processedResults[$item->id]->teams = array();
            }

            // Add Team Details to Player
            if($item->team_id && $item->team_title){
                $teamObject = new \stdClass();
                $teamObject->id = $item->team_id;
                $teamObject->title = $item->team_title;
            }else{
                $teamObject = null;
            }
            if($teamObject) $processedResults[$item->id]->teams[] = $teamObject;
            unset($item->team_id);
            unset($item->team_title);
        }

        return $processedResults;
    }

so in fact I still have this foreach on the player elements but only only one SQL Query to get the information AND i can filter now for teams.