Zend models select join the same table twice

[eburlea]

Hi.

I am using Zend Framework Models. I have 2 tables: 'results' and 'resources'.

Table 'results' has the columns:
'results_id',
'resource_id_team1',
'resource_id_team2',
'match_result'.

Table 'resources' has the columns:
'resource_id',
'resource_name'.

I want to retrieve the results from the table 'results' and I want to join 2 times
the table 'resources', so that to have the names of the teams in the array and to obtain something like:

'results_id' => 1,
'resource_id_team1' => 1,
'resource_id_team2' => 2,
'match_result' => '2:1',
'resource_id' => '1',
'resource_name' => 'barcelona',
'resource_id' => 2,
'resource_name' => 'real_madrid'.

I have tried to get the data with joinLeft, but the problem is the same 'resource_id' and I couldn't succeed.

protected $_name = 'results';

    public function getResults($resource_id_team1, $resource_id_team2)
    {        
        $query = $this->select()
                      ->setIntegrityCheck(false)
                      ->from(array('rt' => $this->_name),'rt.results_id')
                      ->joinLeft(array(
                          'rs'=>'resource'),
                          'rt.resource_id_team1 = rs.resource_id')
                      ->joinLeft(array(
                          'rs'=>'resource'),
                          'rt.resource_id_team2 = rs.resource_id')
                      ->where('resource_id_team1 = ?', $resource_id_team1)
                      ->where('resource_id_team2 = ?', $resource_id_team2)
                      ->where('match_result is NOT NULL');

        return $this->fetchAll($query)->toArray();
    }

Is there a solution, please help.

[eburlea]

I've got it.

protected $_name = 'results';

public function getResults($resource_id_team1, $resource_id_team2)
{
    $query = $this->select()
                  ->setIntegrityCheck(false)
                  ->from(array('rt' => $this->_name))
                  ->joinLeft(array('rs1'=>'resource'),
                      'rt.resource_id_team1 = rs1.resource_id',
                      array('rs1.resource_id_team1' => 'rs1.resource_id', 'rs1.resource_name_team1' => 'rs1.resource_name'))
                  ->joinLeft(array('rs2'=>'resource'),
                      'rt.resource_id_team2 = rs2.resource_id',
                      array('rs2.resource_id_team2' => 'rs2.resource_id', 'rs2.resource_name_team2' => 'rs2.resource_name'))
                  ->where('resource_id_team1 = ?', $resource_id_team1)
                  ->where('resource_id_team2 = ?', $resource_id_team2)
                  ->where('match_result is NOT NULL');
    return $this->fetchAll($query)->toArray();
}