COUNT and GROUP BY using Zend Framework 2 and tableGateway

Question

In Zend Framework 2, using tableGateway, I want to run the following SQL query:

  SELECT categories.category_name, COUNT(forums.forum_id)
    FROM categories LEFT JOIN forums
      ON categories.category_id = forums.category_id
GROUP BY categories.category_name;

Problem is that I simply don't know how to do it. I know how to use $select->join() for example, but I can't figure out how to also do a COUNT and GROUP BY.

What I want with my SQL: I have 2 tables; categories and forums. I want to select all the categories from categories and for each category I want the amount of forums.

Answer 1

Someone on another forum gave me the correct answer, and this works for me. Thought I would share it in case anyone else is having a similar question. Here is how I have it now:

use Zend\Db\Sql\Expression;

$resultSet = $this->tableGateway->select(function (Select $select)
{
    // Select columns and count the forums.
    $select->columns(array(
        'category_name',
        'forumsCount' => new Expression('COUNT(forums.forum_id)')
    ));

    // Left-join with the forums table.
    $select->join('forums', 'categories.category_id = forums.category_id', array(), 'left');

    // Group by the category name.
    $select->group('categories.category_name');
});

return $resultSet;

Answer 2

Your query looks right. Does it work as expected when you run it directly on the database.

I think you might just need to execute the raw query using an adapter.

$sql = "SELECT categories.category_name, COUNT(forums.forum_id) FROM categories LEFT JOIN forums ON     categories.category_id = forums.category_id GROUP BY categories.category_name";

$statement = $this->adapter->query($sql);
return $statement->execute();