title: Executing multiple join with expressions on Zend Framework 2
body: <p>Actually I'm working on a project and I'm looking on how Zend Framework 2 handle complex queries (expecially on how to join n:m tables and how to use GROUP_CONCAT and other functions). Do you know the best practice to execute this query:</p>
  
  <pre><code>SELECT o. * , x.group_one, x.group_two
  FROM table_one AS o
  LEFT JOIN (
  SELECT r.fk1, GROUP_CONCAT( t.field_one ) AS group_one, GROUP_CONCAT( t.field_two ) AS group_two
  FROM table_three AS r
  INNER JOIN table_two AS t ON r.fk2 = t.id
  GROUP BY r.fk1
  ) AS x ON o.id = x.fk1
  LIMIT 0 , 20;
  </code></pre>
  
  <p>using this db schema:</p>
  
  <pre><code>--
  -- Database: `table-test-1`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Structure of table `table_one`
  --
  
  CREATE TABLE `table_one` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `field_1` varchar(255) NOT NULL,
  `field_2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
  
  --
  -- Dump for table `table_one`
  --
  
  INSERT INTO `table_one` (`id`, `field_1`, `field_2`) VALUES
  (1, 'baz', 'bat'),
  (2, 'foo', 'bar'),
  (3, 'foo2', 'bat2'),
  (4, 'fuz', 'bar2'),
  (5, 'poo', 'pee');
  
  -- --------------------------------------------------------
  
  --
  -- Structure of table `table_three`
  --
  
  CREATE TABLE `table_three` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fk1` bigint(20) NOT NULL,
  `fk2` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk1` (`fk1`,`fk2`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
  
  --
  -- Dump for table `table_three`
  --
  
  INSERT INTO `table_three` (`id`, `fk1`, `fk2`) VALUES
  (5, 1, 1),
  (1, 1, 2),
  (6, 1, 4),
  (2, 2, 2),
  (4, 3, 2),
  (7, 3, 3),
  (3, 4, 1),
  (8, 5, 3),
  (9, 5, 4);
  
  -- --------------------------------------------------------
  
  --
  -- Structure of table `table_two`
  --
  
  CREATE TABLE `table_two` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `field_one` varchar(255) NOT NULL,
  `field_two` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
  
  --
  -- Dump for table `table_two`
  --
  
  INSERT INTO `table_two` (`id`, `field_one`, `field_two`) VALUES
  (1, 'label_name_1', 'label_extended_name_1'),
  (2, 'label_name_2', 'label_extended_name2'),
  (3, 'label_name_3', 'label_extended_name_3'),
  (4, 'label_name_4', 'label_extended_name4');
  </code></pre>
  
  <p>At the moment I solved using a Zend\Db\Sql\Sql statement with an handmade query, but I'd like to know if there is, actually, a way to do this with a native Select() (possibly without using Doctrine or similar).</p>
  
  <p>Thank you in advance :)</p>