How to use GROUP_CONCAT with Zend Framework?

Question

Suppose I have a table: students

______________________________________________________ |id | name | school | class | ______________________________________________________ | 1 | John | ABC | C1 | | 2 | Jack | ABC | C1 | | 3 | Anna | ABC | C1 | | 4 | Peter | DEF | D1 | | 5 | Alex | ABC | C2 | | 6 | Bryan | ABC | C2 | | 7 | David | ABC | C2 | | 8 | Cristian | DEF | D1 | _______________________________________________________ 

Using this query:

  SELECT a.class,GROUP_CONCAT(a.name) as names FROM students a WHERE a.school='ABC' GROUP BY a.class 

give me this result:

  ____________________________ |class | names | ____________________________ | C1 | John, Jack, Anna | | C2 | Alex, Bryan, David| ____________________________ 

How to execute this request in the Zend Framework using Zend_Db_Table or Zend_Db_Select? Thank you very much!

Answer 1

I think it will be something like this. Give it a try.

 $table = Your_DbTable_Class(); $select = $table->select() ->setIntegrityCheck(false) ->from(array('a' => 'students'), array( 'class' => 'class' , 'names' => new Zend_Db_Expr('GROUP_CONCAT(a.name)')) ) ->where( 'a.school = ?', 'ABC' ) ->group('a.class'); 

When I collect it, it gives me the following request:

 SELECT `a`.`class`, GROUP_CONCAT(a.name) AS `names` FROM `students` AS `a` WHERE (a.school = 'ABC') GROUP BY `a`.`class` 

Is this what you are looking for?

Answer 2

Zend Expression gave me problems, the easiest way for me was:

 $select->from($this, array( 'listings.*', '(SELECT GROUP_CONCAT(DISTINCT ecg.listing_cat_name,"*|*",ecg.listing_cat_id SEPARATOR "-|-") FROM listings_cats AS ecg LEFT JOIN listings_to_listings_cats ON listings_to_listings_cats.listing_cat_id=ecg.listing_cat_id WHERE listings_to_listings_cats.listing_id=listings.listing_id LIMIT 7 ) AS catGrouping')) 

Here I combine a list of category names and identifiers that I explode in my search results and print a list of interactive categories of children on the parent record. You can obviously add additional connections and other conditions to from (), since the above will be considered as an auxiliary request.