How to apply aliases in SELECT clause when two columns from JOINed tables have the same name?

Question

I am trying to build a gamelist, right now I'm struggling getting the detailed team information.

#__game

id     team_home     team_guest
1      1             2
2      2             3

#__team

id     name
1      My Team Name
2      Another Team Name
3      The third Team

My Code:

// Get the user object.
$user = JFactory::getUser();
// Get the databse object.
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.*');
$query->select('b.name', 'home_name');
$query->select('c.name', 'guest_name');
$query->from($db->quoteName('#__game', 'a'));
$query->join('LEFT', $db->quoteName('#__team', 'b') . ' ON ' . $db->quoteName('a.team_home') . ' = ' . $db->quoteName('b.id'));
$query->join('LEFT', $db->quoteName('#__team', 'c') . ' ON ' . $db->quoteName('a.team_guest') . ' = ' . $db->quoteName('c.id'));
$query->where($db->quoteName('a.published') . ' = 1');
$query->order('a.kickoff ASC');
// Implement View Level Access (if set in table)
if (!$user->authorise('core.options', 'com_component'))
{
    $columns = $db->getTableColumns('#__game');
    if(isset($columns['access']))
    {
        $groups = implode(',', $user->getAuthorisedViewLevels());
        $query->where('a.access IN (' . $groups . ')');
    }
}
$db->setQuery((string)$query);
$items = $db->loadObjectList();
echo '<pre>' . var_export($items,true).'</pre>';
$options = array();
if ($items)
{
    $options[] = JHtml::_('select.option', '', 'Select an option');
    foreach($items as $item)
    {
        //$options[] = JHtml::_('select.option', $item->id, $item->home_name . ' vs ' . $item->guest_name);
    }
}
return $options;

It creates just one "name" inside the return object (sure, its the same key for home and guest) and the guest overwrites the home value.

What do I have to write for the join to get the names for home and guest being placed in different object keys?

Answer 1

Further explanation of the issue:

As you discovered, when two columns in the SELECT clause of a query share the same column name (or alias), the latter value overwrites the former in the result set. To overcome this, differentiate the two repeated column names by assigning a unique alias.

Joomla has a ready-made technique for assigning (or not assigning) aliases within the quoteName() helper method. Simply feed an array of column names as the first parameter and an array of corresponding aliases (or null) as the second parameter. Here is a pre-existing demonstration.

Code Review:

• You are only using the id column and the two team names in the result set, you do not need to use the greedy * to collect all of the column values in the #__game table. Once the number of returned columns is reduced, it becomes more attractive to use a single select() call containing a single quoteName() call.
• qn() is an alias for the quoteName() method. Using these can help to reduce the overall script width/bloat versus the full-word spelling of the method.
• Helper method chaining will also help to reduce the overall size of your query. This means you won't have to keep retyping $query as you build the sql string.
• The relationship between the game table's id and the team table's home/guest ids is absolutely essential (one cannot exist meaningfully without the other). In other words, there is a FOREIGN KEY relationship and so it is more appropriate to use a JOIN (INNER JOIN) instead of a LEFT JOIN. A LEFT JOIN is more forgiving as it will allow the the query to return a missing id to attach to a missing table row and present NULL values. Based on the title of your post (before I edited it), it appeared that you were uncertain of the correct join to use.
• ASC is the default sorting direction, so you may remove these letters and enjoy the same functionality. On the other hand, if you prefer this declarative style, there is no harm in keeping it as is.
• I don't see the full schema of your game table, but I get the impression from the isset() call that you are accommodating the possibility that the table may or may not have a column called. For this reason, it seems like a sensible use of getTableColumns(). However, if the column is always present in the table, then I would remove the access column check in php and just add the where() clause to the query when the user's authorise() check failed.
• There is no need to cast the $query as string type data. This was demonstrated often in earlier versions of Joomla, but is simply unnecessary.
• loadObjectList() produces an empty array when there are no rows in the result set, so assuming you have no syntax failures, you can omit the condition before the foreach().
• Instead of declaring an empty options array, then pushing the "Select an option" option into it, you can do this in one step by declaring the array with the first element inside. Unconditionally loading the static default option will also ensure that the select always has an option (making value markup) -- otherwise, you could potentially have a <select> tag with no "guts".

Implemented suggestions:

$db = JFactory::getDBO();
$query = $db
    ->getQuery(true)
    ->select($db->qn(['a.id', 'b.name', 'c.name'], [null, 'home_name', 'guest_name']))
    ->from($db->qn('#__game', 'a'))
    ->innerJoin($db->qn('#__team', 'b') . ' ON ' . $db->qn('a.team_home') . ' = ' . $db->qn('b.id'))
    ->innerJoin($db->qn('#__team', 'c') . ' ON ' . $db->qn('a.team_guest') . ' = ' . $db->qn('c.id'))
    ->where($db->qn('a.published') . ' = 1')
    ->order('a.kickoff');

$user = JFactory::getUser();
if (!$user->authorise('core.options', 'com_component'))
{
    $columns = $db->getTableColumns('#__game');
    if(isset($columns['access']))
    {
        $groups = implode(',', $user->getAuthorisedViewLevels());
        $query->where('a.access IN (' . $groups . ')');
    }
}

$db->setQuery($query);
$options = [JHtml::_('select.option', '', 'Select an option')];
foreach ($db->loadObjectList() as $item)
{
    $options[] = JHtml::_('select.option', $item->id, $item->home_name . ' vs ' . $item->guest_name);
}
return $options;

Answer 2

Got it now, i had to set the AS in the SELECT as arrays:

    $db = JFactory::getDBO();
    $query = $db->getQuery(true);
    $query->select('a.*');
    $query->select($db->quoteName(array('b.name'),array('home_name')));
    $query->select($db->quoteName(array('c.name'),array('guest_name')));
...