Add expression db_select

Question

What is the corresponding alternative in Drupal 7 for the following query

SELECT row_id,name,email, COUNT(name) AS name_count
FROM tn_api_users 
GROUP BY name
HAVING  (name_count > 1)

Answer 1

That would need a combination of SelectQuery::addExpression(), and SelectQuery::having(), e.g.

$query = db_select('tn_api_users', 'u')
  ->fields('u', array('row_id', 'name', 'email'))
  ->groupBy('name')
  ->having('name_count > 1');

$query->addExpression('COUNT(name)', 'name_count');

Output:

SELECT u.row_id AS row_id, u.name AS name, u.email AS email, COUNT(name) AS name_count
FROM 
{tn_api_users} u
GROUP BY name
HAVING  (name_count > 1)