CODEWITHSUNDEEP
mysqlwordpress
user598952
user598952

Reputation: 3

WordPress Query Help

Trying to build a somewhat complicated WordPress query. In one query, I'm trying to:

  1. Pull all WordPress authors
  2. Only authors with 10 or more published posts(what I'm having trouble with)
  3. Sort authors by latest post.

Here is my original query:

SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name ORDER BY date DESC;

This query returns all authors even those with 9 or less published posts.

Here is the query with the count for the posts:

SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date, COUNT(post_date) as post_count FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name ORDER BY date DESC;

In this query you can see that I've added:

COUNT(post_date) as post_count

Which returns everything beautifully.

It's only when I add this WHERE clause, does the query break

post_count > 9

I get this error message:

Unknown column 'post_count' in 'where clause'

Any idea why this is happing? My theories:

  1. Won't work with grouping
  2. or MySQL doesn't allow more than one AS statement

If you could shed some light, I would greatly appreciate it.

Thanks.

Upvotes: 0

Views: 210

Answers (2)

A. Rager
A. Rager

Reputation: 1981

You're looking for the "HAVING" operator.

SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date, COUNT(post_date) as post_count FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name HAVING post_count>9 ORDER BY date DESC;

Upvotes: 1

Ass3mbler
Ass3mbler

Reputation: 3925

change the condition in the WHERE part of the query post_count > 9

with HAVING post_count > 9 after the GROUP BY

the final query will be SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date, COUNT(post_date) as post_count FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name HAVING post_count > 9 ORDER BY date DESC;

Upvotes: 1

Related Questions