> SELECT t.id AS t__id, t.date AS t__date, t2.id AS t2__id, t2.name AS t2__name, p.id AS p__id
FROM tournament t
INNER JOIN tournament_site t2 ON t.site_id = t2.id
INNER JOIN player p ON t2.id = p.site_id; 

+-------+---------------------+--------+--------------+-------+
| t__id | t__date             | t2__id | t2__name     | p__id |
+-------+---------------------+--------+--------------+-------+
|     1 | 2007-12-29 19:00:00 |      2 | Full Tilt    |     1 |
|     2 | 2008-01-01 19:00:00 |      2 | Full Tilt    |     1 |
|     3 | 2007-12-01 18:30:00 |      2 | Full Tilt    |     1 |
|     1 | 2007-12-29 19:00:00 |      2 | Full Tilt    |     4 |
|     2 | 2008-01-01 19:00:00 |      2 | Full Tilt    |     4 |
|     3 | 2007-12-01 18:30:00 |      2 | Full Tilt    |     4 |
|     4 | 2007-12-29 19:00:00 |      3 | PokerStars   |     2 |
|     5 | 2008-01-01 19:00:00 |      3 | PokerStars   |     2 |
|     6 | 2007-12-01 18:30:00 |      3 | PokerStars   |     2 |
|     4 | 2007-12-29 19:00:00 |      3 | PokerStars   |     5 |
|     5 | 2008-01-01 19:00:00 |      3 | PokerStars   |     5 |
|     6 | 2007-12-01 18:30:00 |      3 | PokerStars   |     5 |
|     7 | 2007-12-29 19:00:00 |      4 | Ultimate Bet |     3 |
|     8 | 2008-01-01 19:00:00 |      4 | Ultimate Bet |     3 |
|     9 | 2007-12-01 18:30:00 |      4 | Ultimate Bet |     3 |
|     7 | 2007-12-29 19:00:00 |      4 | Ultimate Bet |     6 |
|     8 | 2008-01-01 19:00:00 |      4 | Ultimate Bet |     6 |
|     9 | 2007-12-01 18:30:00 |      4 | Ultimate Bet |     6 |
+-------+---------------------+--------+--------------+-------+
18 rows in set (0.00 sec)



 65     $tournaments = Doctrine_Query::create()
 66       ->select('t.date, s.name, p.id')
 67       ->from('Tournament t')
 68       ->innerjoin('t.Site s')
 69       ->innerjoin('s.Players p')
 70       ->setHydrationMode(Doctrine::HYDRATE_ARRAY)
 71       ->execute();
 72     $this->tournaments = $tournaments;

result of 

  3
  4 <ul>
  5 <?php foreach ($tournaments as $t): ?>
  6   <li>Player ID: <?php echo $t['Site']['Players'][0]['id']; ?></li>
  7 <?php endforeach; ?>
  8 </ul>


is:

Player ID: 4
Player ID: 
Player ID: 
Player ID: 5
Player ID: 
Player ID: 
Player ID: 6
Player ID: 
Player ID:


HOWEVER....

using object  hydration...


output is:


Player ID: 1
Player ID: 1
Player ID: 1
Player ID: 2
Player ID: 2
Player ID: 2
Player ID: 3
Player ID: 3
Player ID: 3

and the following extra queries are run:

SELECT p.id AS p__id, p.screenname AS p__screenname, p.site_id AS p__site_id, p.user_id AS p__user_id, p.staking_balance AS p__staking_balance FROM player p WHERE p.site_id IN (?) - (2 )
SELECT p.id AS p__id, p.screenname AS p__screenname, p.site_id AS p__site_id, p.user_id AS p__user_id, p.staking_balance AS p__staking_balance FROM player p WHERE p.site_id IN (?) - (3 )
SELECT p.id AS p__id, p.screenname AS p__screenname, p.site_id AS p__site_id, p.user_id AS p__user_id, p.staking_balance AS p__staking_balance FROM player p WHERE p.site_id IN (?) - (4 )


array(9) {
  [0]=>
  array(3) {
    ["id"]=>
    string(1) "1"
    ["date"]=>
    string(19) "2007-12-29 19:00:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "2"
      ["name"]=>
      string(9) "Full Tilt"
      ["Players"]=>
      array(1) {
        [0]=>
        array(1) {
          ["id"]=>
          string(1) "4"
        }
      }
    }
  }
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["date"]=>
    string(19) "2008-01-01 19:00:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "2"
      ["name"]=>
      string(9) "Full Tilt"
      ["Players"]=>
      array(1) {
        [0]=>
        NULL
      }
    }
  }
  [2]=>
  array(3) {
    ["id"]=>
    string(1) "3"
    ["date"]=>
    string(19) "2007-12-01 18:30:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "2"
      ["name"]=>
      string(9) "Full Tilt"
      ["Players"]=>
      array(1) {
        [0]=>
        NULL
      }
    }
  }
  [3]=>
  array(3) {
    ["id"]=>
    string(1) "4"
    ["date"]=>
    string(19) "2007-12-29 19:00:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "3"
      ["name"]=>
      string(10) "PokerStars"
      ["Players"]=>
      array(1) {
        [0]=>
        array(1) {
          ["id"]=>
          string(1) "5"
        }
      }
    }
  }
  [4]=>
  array(3) {
    ["id"]=>
    string(1) "5"
    ["date"]=>
    string(19) "2008-01-01 19:00:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "3"
      ["name"]=>
      string(10) "PokerStars"
      ["Players"]=>
      array(1) {
        [0]=>
        NULL
      }
    }
  }
  [5]=>
  array(3) {
    ["id"]=>
    string(1) "6"
    ["date"]=>
    string(19) "2007-12-01 18:30:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "3"
      ["name"]=>
      string(10) "PokerStars"
      ["Players"]=>
      array(1) {
        [0]=>
        NULL
      }
    }
  }
  [6]=>
  array(3) {
    ["id"]=>
    string(1) "7"
    ["date"]=>
    string(19) "2007-12-29 19:00:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "4"
      ["name"]=>
      string(12) "Ultimate Bet"
      ["Players"]=>
      array(1) {
        [0]=>
        array(1) {
          ["id"]=>
          string(1) "6"
        }
      }
    }
  }
  [7]=>
  array(3) {
    ["id"]=>
    string(1) "8"
    ["date"]=>
    string(19) "2008-01-01 19:00:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "4"
      ["name"]=>
      string(12) "Ultimate Bet"
      ["Players"]=>
      array(1) {
        [0]=>
        NULL
      }
    }
  }
  [8]=>
  array(3) {
    ["id"]=>
    string(1) "9"
    ["date"]=>
    string(19) "2007-12-01 18:30:00"
    ["Site"]=>
    array(3) {
      ["id"]=>
      string(1) "4"
      ["name"]=>
      string(12) "Ultimate Bet"
      ["Players"]=>
      array(1) {
        [0]=>
        NULL
      }
    }
  }
}