4

I have this SQL query:

SELECT pais FROM pais LEFT OUTER JOIN users_has_pais ON pais.id = users_has_pais.pais_id WHERE users_has_pais.users_id = 100

And I'm trying to write something similar within a model using the leftJoin method from Zend_Db_Table but I have no clue on what I'm doing.... I tried with something like this:

$resultSetPais = Zend_Db_Table::getDefaultAdapter();
$some = $resultSetPais->select()
                      ->joinLeft( array ( 'users_has_pais' => 'users' ),
                          'pais.id = users_has_pais.pais_id', 'pais' );

But truth is I have no idea how to make it work and this code just returns the adapter information.

SOLVED:

        $instance = Zend_Db_Table_Abstract::getDefaultAdapter();
        $pais = $instance->select();
        $pais->from(array('p' => 'pais'), array('p.pais') )
             ->join( 'users_has_pais', 'p.id = users_has_pais.pais_id' )
             ->where( 'users_has_pais.users_id = ?', $row->id );
        $paisEntry = $instance->fetchCol($pais);
la_f0ka
  • 1,773
  • 3
  • 23
  • 44
  • add your solution as an answer and mark it completed – JamesHalsall May 26 '11 at 11:32
  • thanks for making me notice that @Jaitsu, I didn't do it sooner because I tried answering another question I had asked a couple of days ago and I couldn't do it. Actually... I cannot mark my answer as correct until tomorrow. – la_f0ka May 26 '11 at 11:54

2 Answers2

1

I'm adding the answer to the question as suggested by @Jaitsu. For this kind of left join:

SELECT pais FROM pais LEFT OUTER JOIN users_has_pais ON pais.id = users_has_pais.pais_id WHERE users_has_pais.users_id = 100

The code should be something like:

    $instance = Zend_Db_Table_Abstract::getDefaultAdapter();
    $pais = $instance->select();
    $pais->from(array('p' => 'pais'), array('p.pais') )
         ->join( 'users_has_pais', 'p.id = users_has_pais.pais_id' )
         ->where( 'users_has_pais.users_id = ?', $row->id );
    $paisEntry = $instance->fetchCol($pais);
la_f0ka
  • 1,773
  • 3
  • 23
  • 44
-1

hey this code produces an INNER JOIN sql query, not an OUTER JOIN - it is different thing, right? so, what should be the correct way of doing OUTER JOIN?

heheheheheheee
  • 19
  • 1