How to get all rows from related table using join in Zend Framework

Question

I have two tables. First table contain offer details and second table contains offer collection of languages(collection can be selected in main offer form). I want to use query with "join" language table to get all of them in single query. How it's look like: Relation

Trying to get all of the offers I want to get all offer languages as one field.

     array [
      'id' => string '11',
      'name' => string '134',
      'date' => string '01-12-2016',
      'languages' => array(all language value from related table)
     ]

Here is my query:

        $select = $this->getDbTable()->select()
            ->setIntegrityCheck(false)
            ->from(array('o' => 'offers'), array('o.*'))
            ->joinLeft(array('ol' => 'offer_language'), 'ol.id_offer = o.id', array('ol.*'));

    $resultSet = $this->getDbTable()->fetchAll($select)

In that way if one offer has a three language then I get three same offer with different language field.

Zend Version: 1.11

Thanks for help.

score 0 · Answer 1 · edited May 23 '17 at 12:32

The solution is to use a Zend_Db_Expr with GROUP_CONCAT. Here are two posts that will give you a clear explanation: Can I concatenate multiple MySQL rows into one field? and How to use GROUP_CONCAT with Zend Framework?

Your code will end up looking something like this:

$select = $this->getDbTable()->select()
          ->setIntegrityCheck(false)
          ->from(array('o' => 'offers'), array('o.*'))
          ->joinLeft(array('ol' => 'offer_language'), 'ol.id_offer = o.id', array('languages' => new Zend_Db_Expr('GROUP_CONCAT(ol.language)')))
          ->group('o.id');

Good luck!

How to get all rows from related table using join in Zend Framework

1 Answers1