Tell me more ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

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))');
share|improve this question
add comment (requires an account with 50 reputation)

1 Answer

up vote 1 down vote accepted

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();
share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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