TYPO3 Core

A call like:

$GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
  'tx_projectfiles_projects.number, tx_projectfiles_projects.title, tx_projectfiles_projects.owner, tx_projectfiles_user.uid',
  'tx_projectfiles_projects INNER JOIN tx_projectfiles_projects_user ' .
    'ON tx_projectfiles_projects_user.uid_foreign=tx_projectfiles_projects.uid ' .
    'INNER JOIN tx_projectfiles_user ' .
    'ON tx_projectfiles_user.uid=tx_projectfiles_projects_user.uid_local',
  'tx_projectfiles_user.username=\'myusername\''
);

is correctly translated to the corresponding fields in the actual database, but when returning the results only the fields from tx_projectfiles_projects are mapped back.

in map_assocArray() it turns out that the $tables array only contains the first table and the other joined tables are present as additional information:

array( 0 => array (
  'table' => 'tx_projectfiles_projects',
  'JOIN' => array (
    0 => array (
      'type' => 'INNER JOIN',
      'withTable' => 'tx_projectfiles_projects_user',
      'ON' => array (
        0 => array (
          'operator' => '',
          'left' => array (
            'table' => 'tx_projectfiles_projects_user',
            'field' => 'uid_foreign'
          ),
          'comparator' => '=',
          'right' => array (
            'table' => 'tx_projectfiles_projects',
            'field' => 'uid'
          )
        )
      )
    ),
    1 => array (
      'type' => 'INNER JOIN',
...
)

Before reverse mapping the field names are:
projectnr, projectname, ownerid, userid (like in the "remote" database)
, after mapping we have:
number, title, owner and userid

The last one should refer to tx_projectfiles_user.uid (on the TYPO3 side) and should have been labelled as 'uid'. For some reason not all tables involved in the query are sent to map_assocArray().

The source states that there is a problem when fields have the same name in different tables, but since we should use in such a case <table.column> it should not be a problem if there are no duplicate names (even without table name) in the list of columns in the SELECT part of the query.

I thought about executing the query with the special mm_query function, but this is only a shell which constructs the joins and DBAL only kicks in after that. It doesn't make any difference for this problem.