Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.Here's my problem hopefully someone can help!
I'm trying to make a view/report which shows the number of nodes posted per DAY. So the first column of each row will be the date (day) like Feb 05 2012, then the next column will be a count of the number of nodes posted on that day (like 37). So this is a view of nodes, with aggregation, doing a "count" on the NID field, and setting the sort criteria to "post date" so the aggregation knows to aggregate on post date.
No matter how I change the granularity of post date, the aggregation stays in "seconds". Meaning I get how many nodes are posted at exactly the same second, but not how many are posted on a given day, I get duplicate rows. etc.
I think this is a bug, because I'm easily able to fix this and get the correct result by modifying the output query and running it manually.
Here's the query that views produces:
SELECT node.created AS node_created, COUNT(node.nid) AS nid, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL -18000 SECOND), '%Y%m%d') AS node_created_day
FROM
{node} node
WHERE (( (node.status = '1') ))
GROUP BY node_created
ORDER BY node_created_day DESC
If it could just be changed to this, it would work. Note the GROUP BY clause using "node_created_day" instead of just "node_created"
SELECT node.created AS node_created, COUNT(node.nid) AS nid, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL -18000 SECOND), '%Y%m%d') AS node_created_day
FROM
{node} node
WHERE (( (node.status = '1') ))
GROUP BY node_created_day
ORDER BY node_created_day DESC
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | 1431780-aggregate-formulae.patch | 956 bytes | wodenx |
| #2 | 1431790-example-view.txt | 4.76 KB | wodenx |
Comments
Comment #1
dawehnerThe funny thing here is that this is actually invalid sql. Could you be so kind and provide an export to the view, so it's possible to see how it's actually configured, maybe this gives some ideas.
Comment #2
wodenx CreditAttribution: wodenx commentedI had this problem in 6.x-3.x, but was able to resolve it there by changing the aggregation settings on the date field (not the date sort field) to "min" or "max" - this removed that field from the GROUP BY, and left me with:
which worked (except for the fact that the formatting for the aggregated date field was lost -- but that's another issue).
But this doesn't seem to work in D7 - the GROUP BY for the sort field is never added to the query. The attached patch seems to fix this. I've also attached an export of the faulty view.
I also think it would be good to provide granularity settings for the default date field handler -- that way it could be used for grouping without having to add the ORDER BY as well (in case you want to sort by something else). I would be willing to submit a patch for this if it is thought desirable.
Comment #3
tim.plunkettTriggering the testbot.
Comment #4
dawehnerAdd a tag
Comment #5
tim.plunkettChanging version for the testbot.
Comment #6
dawehnerJust played a bit with the problem and your patch is totally right, thank you!
Committed the patch to 7.x-3.x
Comment #8
mototribe CreditAttribution: mototribe commentedI didn't try the patch but your trick to change it to min or max worked great. Thanks!
Comment #9
lunk rat CreditAttribution: lunk rat commentedWoo-hoo! Thanks for the tip in #2. I actually had to change my sort criteria aggregation settings (not the field aggregation) to 'Minimum' and then the granularity worked for my aggregated date field!
What an awesome trick!
Comment #10
Rohit_Rajput CreditAttribution: Rohit_Rajput commented2: 1431780-aggregate-formulae.patch queued for re-testing.
Comment #12
Leeteq CreditAttribution: Leeteq commentedRef. also this module that handles the same, AFAICS;
https://www.drupal.org/project/views_date_format_sql