2

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))');
3

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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