$q = Doctrine_Query::create()
            ->from('AGENT ag')
            ->leftJoin('ag.CHANTIER_AGENT cag)
            ->orderBy('ag.nom')
            ->limit(10)
            ->offset(10)
            ->execute();

 
SELECT a.pk AS a__pk, a.ts AS a__ts, a.ck_agent AS a__ck_agent, a.matricule AS a__matricule, a.nom AS a__nom, a.prenom AS a__prenom, a.agent_maitrise AS a__agent_maitrise, 
c.pk AS c__pk, c.ts AS c__ts, c.ck_chantier_agent AS c__ck_chantier_agent, c.ek_chantier AS c__ek_chantier, c.fk_chantier AS c__fk_chantier, c.ek_agent AS c__ek_agent, c.fk_agent AS c__fk_agent 
FROM AGENT a 
LEFT JOIN CHANTIER_AGENT c ON a.ck_agent = c.ek_agent 
WHERE a.ck_agent IN 
(SELECT b.ck_agent 
FROM ( SELECT a.*, ROWNUM AS doctrine_rownum 
FROM ( SELECT DISTINCT a2.ck_agent, a2.nom FROM AGENT a2 LEFT JOIN CHANTIER_AGENT c2 ON a2.ck_agent = c2.ek_agent ORDER BY a2.nom ) 
a2 ) 
b 
WHERE doctrine_rownum BETWEEN 11 AND 20) 
ORDER BY a.nom

 
                    $query= 'SELECT '.$this->quoteIdentifier('b').'.'.$column.' FROM ( '.
                                 'SELECT '.$this->quoteIdentifier('a').'.*, ROWNUM AS doctrine_rownum FROM ( '
                                   . $query . ' ) ' . $this->quoteIdentifier('a') . ' '.
                              ' ) ' . $this->quoteIdentifier('b') . ' '.
                              'WHERE doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;

 
                    $query = 'SELECT '.$this->quoteIdentifier('limb').'.'.$column.' FROM ( '.
                                 'SELECT '.$this->quoteIdentifier('lima').'.*, ROWNUM AS doctrine_rownum FROM ( '
                                   . $query . ' ) ' . $this->quoteIdentifier('lima') . ' '.
                              ' ) ' . $this->quoteIdentifier('limb') . ' '.
                              'WHERE doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;