Zend FR

titan · 03-02-2010 17:45:31

Bonjour,

J'ai une requête que je voudrais exécuter avec ZEND DB, mais je ne trouve pas d'exemple se rapprochant pour l'adapter...

Voici ma requête (mysql) :

Code:

        
$sql = 'SELECT 
        t1.title AS lev1, 
        t2.title AS lev2, 
        t3.title AS lev3, 
        t4.title AS lev4, 
        t5.title AS lev5, 
        t6.title AS lev6, 
        t7.title AS lev7
        FROM pages AS t1
        LEFT JOIN pages AS t2 ON t2.id_parent_page = t1.id
        LEFT JOIN pages AS t3 ON t3.id_parent_page = t2.id
        LEFT JOIN pages AS t4 ON t4.id_parent_page = t3.id
        LEFT JOIN pages AS t5 ON t5.id_parent_page = t4.id
        LEFT JOIN pages AS t6 ON t6.id_parent_page = t5.id
        LEFT JOIN pages AS t7 ON t7.id_parent_page = t6.id
        WHERE t1.id = "33"';

Voici le schéma :

en espérant un peu d'aide, merci

sekaijin · 03-02-2010 18:32:57

Code:

        $select = $this->_db->select()
        ->distinct()
        ->from('to_deploy', array(
            'cmp_dir',
            'cmp_id',
            'cmp_name',
            'cmp_flx_id' => 'flx_id', // ICI to_deploy.flx_id AS cmp_flx_id
            'cnx_dir',
            'cnx_id',
            'cnx_type',
            'cnx_name',
            'site_code',
            'site_id',
            'app_name'
        ))
        ->join('flux', 'to_deploy.flx_id = flux.flx_id', array(
            'flx_id',
            'flx_name'
        ))
        ->join('ressource', 'to_deploy.res_id = ressource.res_id', array(
            'res_id',
            'res_name',
            'res_host', 
            'res_type',
            'res_port', 
            'res_user',
            'res_pass', 
            'res_in', 
            'res_out', 
            'res_staging', 
            'res_retry',
            'res_parser', 
            'sap_client', 
            'sap_group', 
            'sap_system_id', 
            'sap_message_host', 
            'sap_gateway_host', 
            'sap_gateway_service' 
        ))
        ->where('flx_code = ;flx_code')
        ->order('site_code')
        ->order('cmp_name')
        ->order('cnx_name')
        ->order('res_name')

On peut aller plus loin dans la jointure avec des clause complexe comme

Code:

        $select = $this->_db->select()
        ->distinct()
        ->from('component', array(
            'cmp_id',
            'flx_id',
            'cmp_name',
            'cmp_dir',
            'site_id'
        ))
        ->joinLeft('site', '(component.site_id = site.site_id) AND 
                    (site.flx_id NOT IN (
                          $this->_db->select()
                          ->from('flow', array('flx_id')
                          ->where("flx_code = 'TRX'")
                    ))', array('site_code'))
        ->where('flx_id = ?')
        ->order('cmp_name')

pour obtenir

Code:

LEFT JOIN pages AS t2

il faut faire

Code:

->joinLeft(array('t2' => 'pages'), ....

A+JYT

Dernière modification par sekaijin (03-02-2010 18:35:26)

titan · 04-02-2010 17:27:16

Merci, je vais étudier ça de plus près.

Zend FR

#1 03-02-2010 17:45:31

requête avec jointures

Code:

#2 03-02-2010 18:32:57

Re: requête avec jointures

Code:

Code:

Code:

Code:

#3 04-02-2010 17:27:16

Re: requête avec jointures

Pied de page des forums