GROUP_CONCAT with JOINLEFT in Zend Db Select

Question

Assuming that I have 2 tables

articles
  id              title
  1               Article 1
  2               Article 2


Images
  id              article_id     image
  1               1              a.png
  2               1              b.png
  3               2              c.png
  4               2              d.png

All that I want is retreive all articles with their images.

For example:

article_id     title           images
1              Article 1       a.png, b.png
2              Article 2       c.png, d.png

How could I do that with Zend_Db_Select?

I tried something like this but had no luck:

$select = $this->getDbTable()->select()->setIntegrityCheck(false)->distinct();
$select->from(array('a'=>'articles'))
  ->joinLeft(array('i'=>'images'),'i.article_id=a.id',array('images'=> new
               Zend_Db_Expr('GROUP_CONCAT(i.image)')));

It returns just only 1 row which 'images' field contains images of both articles.

article_id     title           images
1              Article 1       a.png, b.png, c.png, d.png

What am i doing wrong here?

Langan

Answer 1

You have not used group by clause in query.

Try below:

$select->from(array('a'=>'articles'))
  ->joinLeft(
       array('i'=>'images'),
       'i.article_id=a.id',
       array('images'=> new Zend_Db_Expr('GROUP_CONCAT(i.image)')))
  ->group('a.id');

Unction

Adding "GROUP BY" solved the problem. OR using this solves it too: $select->joinLeft(array('x'=> new Zend_Db_Expr('(SELECT i.article_id, GROUP_CONCAT(i.image) as images FROM images as i GROUP BY i.article_id)')), 'x.article_id=a.id', array('x.images')); – Citizen

Your answer really helped me – Teleost