CODEWITHSUNDEEP
phpmysqlsqlzend-framework
raj_arni
raj_arni

Reputation: 1007

Zend Framework 2 how to get columns from multiple tables using join?

I am using ZfcBase\Mapper\AbstractDbMapper to write mysql queries for my project. Everything works great for single table queries. If I join with some tables the results are good but the only problem is I dont know how to select columns from "joined" tables. Here is my sample code:

class XYZ extends AbstractDbMapper implements XYZInterface

    {
    public function joinTables() {
         $select = $this->getSelect();
         $select->reset('columns');
         $select->columns(array('colA','colB'))
                   ->join('ABC','ABC.colA=XYZ.colA',array('colB','colC'))
                   ->where(array('XYZ.colA' => 'value1'));
         $resultSet = $this->select($select);
         $myResults= array();
         foreach ($resultSet as $myResult) {
                $myResults[] = $myResult;
         }
         return $myResults;
    }

}

The result $myResults is actually an array of XYZ entities. How can I have the ABC entities part of my result set $myResults?

Upvotes: 0

Views: 1413

Answers (2)

dixromos98
dixromos98

Reputation: 754

//join with columns

    $select11 = new Select;
    $select11->from('foo')->join('zac', 'm = n', array('bar', 'baz'));
   //'SELECT "foo".*, "zac"."bar" AS "bar", "zac"."baz" AS "baz" FROM "foo" INNER JOIN "zac" ON "m" = "n"';

Considering the above example found at examples. Your code should look like this.

 $select = $this->getSelect();
 $select->from('ABC')->join('XYZ','ABC.colA=XYZ.colA' array('colA','colB'))
                   ->where('XYZ.colA = value1');
         $resultSet = $this->select($select);
         $myResults= array();

Upvotes: 0

Daan
Daan

Reputation: 12236

Add ->setIntegrityCheck(false);

I've updated your code:

public function joinTables() {
     $select = $this->getSelect();
     $select->setIntegrityCheck(false);
     $select->reset('columns');
     $select->columns(array('colA','colB'))
               ->join('ABC','ABC.colA=XYZ.colA',array('colB','colC'))
               ->where(array('XYZ.colA' => 'value1'));
     $resultSet = $this->select($select);
     $myResults= array();
     foreach ($resultSet as $myResult) {
            $myResults[] = $myResult;
     }
     return $myResults;
}

Upvotes: 0

Related Questions