6

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)

1 Answer 1

6

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)
6
  • wow, that was fast !..... Thanks Clive. The following query will give me the result of the persons with same "name". Any idea how can I display all the records ? Commented Oct 19, 2013 at 13:53
  • Hmmm not too sure, but what about adding ->groupBy('row_id') to the chain before the name group by? That would probably ruin your counting though. It's going to be tricky to get individual results if you're grouping the query on that column
    – Clive
    Commented Oct 19, 2013 at 13:55
  • It is a bit tricky indeed, Unfortunately code->groupBy('row_id')code didn't work out. I will just create an array and it will be two-step process. Thanks again ! Commented Oct 19, 2013 at 13:58
  • 1
    Not very relevant to this question but still I find this web service jonduell.com/sql-parser to be very useful to build db_select() statements for given query.
    – Sivaji
    Commented Oct 20, 2013 at 6:02
  • @Sivaji That tools a great idea, unfortunately it fails for the query given in this question
    – Clive
    Commented Oct 20, 2013 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.