1

I need to find users who have not uploaded certain contents in last 30 days. I have tried this query it is not working. Can anyone help me?

select uid, max(created) as max_created from node where node.type in ('page','story') AND max_created < UNIX_TIMESTAMP()-2592000

I am getting error in phpmyadmin:

1054 - Unknown column 'max_created' in 'where clause'

closed as off-topic by kiamlaluno Jun 16 '16 at 9:57

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on programming, PHP, SQL, etc. that do not relate directly to Drupal are off-topic here, but can be asked on Stack Overflow." – kiamlaluno
If this question can be reworded to fit the rules in the help center, please edit the question.

3

SQL is evaluated backwards, from right to left. So the WHERE clause is parsed and evaluate prior to the SELECT clause. Because of this the aliasing of MAX(created) to max_created not yet occurred.

You can try this,

SELECT uid, MAX(created) FROM node WHERE node.type IN ('page','story') HAVING MAX(created) < UNIX_TIMESTAMP()-2592000

  • Not sure why you'd need to re-query the same table, just AND MAX(created) < UNIX_TIMESTAMP()-2592000 would be perfectly sufficient. +1 for the explanation though – Clive Jun 5 '14 at 16:45
  • I get this error with I do that #1111 - Invalid use of group function – shrish Jun 5 '14 at 16:49
  • Oops yeah you're right, not sure where my head was at. Correct query would be SELECT uid, MAX(created) FROM node WHERE node.type IN ('page','story') HAVING MAX(created) < UNIX_TIMESTAMP()-2592000 – Clive Jun 5 '14 at 16:52
  • @ARUN You'll probably want to GROUP BY uid in that case – Clive Jun 5 '14 at 17:00
  • @Clive So Both Having and Group by are needed? – ARUN Jun 5 '14 at 17:01

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