Writing subquery using Zend DB containing GROUP_CONCAT

Question

I have problem how to rewrite complex SELECT to Zend syntax. SELECT is as SUBSELECT and GROUP_CONCAT make it more complex.

select * from Atrybutypowiazania as ap
left join (
    select a.id_gr,
    group_concat(a.nazwa SEPARATOR ', ') 
    from Atrybutygrupy as ag
    left join Atrybuty a on a.id_gr = ag.id
    where ag.jedn_miary = 'rozmiar'
    group by ag.id 
) qqq on qqq.id_gr = ap.id_gr
left join Produkty p on p.id = ap.id_og
where qqq.id_gr <> ''
group by ap.id_og

I tried insert pure SELECT into query

$sql = "select a.id_gr,
            group_concat(a.nazwa SEPARATOR ', ') 
            from Atrybutygrupy as ag
            left join Atrybuty a on a.id_gr = ag.id
            where ag.jedn_miary = 'rozmiar'
            group by ag.id ";

$select = $this->db->select()
                ->from(array('ap' => 'Atrybutypowiazania'))
                ->joinleft(array($sql => 'qqq'), 'qqq.id_gr = ap.id_gr')
                ->joinleft(array('p' => 'Produkty'), 'p.id = ap.id_og')
                ->where($where)
                ->group('ap.id_og')
                ;

        $result = $this->db->fetchAll($select);

but it doesn't work

This may help: http://stackoverflow.com/questions/6329077/how-to-use-group-concat-with-zend-framework or http://stackoverflow.com/questions/10086136/group-concat-with-joinleft-in-zend-db-select — xQbert, Jun 02 '16 at 18:04

score 0 · Answer 1 · answered Jun 02 '16 at 21:04

I have found the solution:

$sql = $this->db->select()
        ->from(array('ag' => 'Atrybutygrupy'), array('idgr' => 'a.id_gr', 'myRozmiary' => new Zend_Db_Expr('GROUP_CONCAT(a.nazwa SEPARATOR ", ")')))
        ->joinleft(array('a' => 'Atrybuty'), 'a.id_gr = ag.id' )
        ->where($where1)
        ->group('ag.id');

$select = $this->db->select()
        ->from(array('ap' => 'Atrybutypowiazania'))
        ->joinleft(array('qqq' => $sql), 'qqq.id_gr = ap.id_gr')
        ->joinleft(array('p' => 'Produkty'), 'p.id = ap.id_og')
        ->where($where)
        ->group('ap.id_og')
        ;

$result = $this->db->fetchAll($select);

Writing subquery using Zend DB containing GROUP_CONCAT

1 Answers1