building a query with the database api and groupby multiple fields in drupal 7

Question

first of all I simplified my query so it might be more useable to other users on this site.

I'm looking for a solution to use group by on multiple fields.

This query selects the values of the pop_links module where only unique values can remain. resulting in 1 value per day, node and hostname. ( to display only one click per hostname a day )

Here's my plain sql query:

SELECT cid, nid, from_unixtime(timestamp) FROM pop_links_stats p
GROUP BY date(from_unixtime(timestamp)), hostname, nid ORDER BY timestamp

Here is a simplified version of the drupal 7 query I'm currently using:

$query = db_select('pop_links_stats','p');
$query->fields('p', array('cid','nid','timestamp'));
$results = $query->execute()->fetchAll();

I'm also already using the join method of the db api but I don't know how to add the above conditions.

Adding this below will group the fields by every day but I want to groupby on 3 fields.

$query->groupBy('DATE(FROM_UNIXTIME(p.timestamp))');

Answer 1

Turns out you you can 'stack' the groupby methods like so:

$query->groupBy('DATE(FROM_UNIXTIME(p.timestamp))')->groupBy('nid')->groupBy('hostname');

Which generates the following query string under the hood:

GROUP BY DATE(FROM_UNIXTIME(p.timestamp)), nid, hostname

resulting in the following drupal query for this question:

$query = db_select('pop_links_stats','p');
$query->fields('p', array('cid','nid','timestamp'));
$query->groupBy('DATE(FROM_UNIXTIME(p.timestamp))')->groupBy('nid')->groupBy('hostname');
$results = $query->execute()->fetchAll();