Question 91633 in Wordpress Question posted in
The official Wordpress documentation can be found here.

MySQL WordPress database: How to find users who have not posted since a number of weeks, as I can't use max(date) in select clause?

Here is what I am at right now, but it does not return anything

select wp_users.user_nicename, wp_users.user_email, max(yearweek(wp_wpforo_posts.created)) as w
from LeBearCNC.wp_users, wp_wpforo_posts
where wp_users.ID = wp_wpforo_posts.userid
and not exists (select max(yearweek(wp_wpforo_posts.created)) where wp_users.ID = wp_wpforo_posts.userid) /*and yearweek(current_date()) - 12 < yearweek(wp_wpforo_posts.created))*/
group by wp_users.user_nicename, wp_users.user_email
order by w desc;

if I put the max(yearweek(wp_wpforo_posts.created)) in the where clause, I get an error "invalid use of group function"

Forgot to precise that wp_wpforo tables are for a forum plugin named wpForo

I managed to get what I want, using a temporary table, but I believe I should be able to bypass the need for this temporary table. I just don’t know how:

     create temporary table if not exists lastpost as 
(select wp_wpforo_posts.userid,max(date(wp_wpforo_posts.created)) as w
from wp_wpforo_posts
group by userid);

SELECT wp_users.display_name, wp_users.user_email, lastpost.w
from wp_users, lastpost
where lastpost.userid = wp_users.ID
and date_add(lastpost.w, interval 6 month) < date(current_date())
order by w desc;

Thanks for you help,
Bernard.

Tags:

2

Answers


  1. 0

    you can use subqueries as temporary tabels and notice is use standrd sql JOIN for my queries, as this is standrad nowadays

    SELECT wp_users.display_name, wp_users.user_email, lastpost.w
from wp_users JOIN (select wp_wpforo_posts.userid,max(date(wp_wpforo_posts.created)) as w
from wp_wpforo_posts
group by userid) lastpost
ON lastpost.userid = wp_users.ID
WHERE date_add(lastpost.w, interval 6 month) < current_date()
order by w desc;
    Login or Signup to reply.
  2. 0

    You could try to use this query

    SELECT u.user_nicename,
       u.user_email,
       MAX(YEARWEEK(p.created)) AS w
FROM   lebearcnc.wp_users u 
         INNER JOIN wp_wpforo_posts p ON u.id = p.userid
WHERE  NOT EXISTS (SELECT 1
                   FROM   wp_wpforo_posts p1
                   WHERE  p1.userid = u.id
                          AND YEARWEEK(p1.created) > YEARWEEK(current_date()) - 12)
GROUP  BY u.user_nicename, u.user_email
ORDER  BY w DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search