exclude pingbacks from wordpress SQL query

Question

exclude pingbacks from wordpress SQL query

Marc

2016年12月31日 17:45

Does anybody know how to exclude pingbacks from this query? I tried to add AND post_type = 'comment' after AND post_password = '' but this didn't work.

$query = "select
           wp_posts.*,
           coalesce((
           select
              max(comment_date)
           from
              $wpdb-comments wpc
           where
              wpc.comment_post_id = wp_posts.id
              AND comment_approved = 1
              AND post_password = ''
          ),
           wp_posts.post_date  ) as mcomment_date
        from
           $wpdb-posts wp_posts
        where
           post_type = 'post'
           and post_status = 'publish'
           and comment_count  '0'
        order by
   mcomment_date desc  limit $limit";

Happy New Year! Even if you might not know the answer ;-)

Topic pingbacks query Wordpress sql

Category Web

Tunji · Accepted Answer · 2016年12月31日 17:45

You can achieve this by excluding pingback from the comment_type column of wordpress comments table

$query = "select
           wp_posts.*,
           coalesce((
           select
              max(comment_date)
           from
              $wpdb->comments wpc
           where
              wpc.comment_post_id = wp_posts.id
              AND comment_approved = 1
              AND post_password = ''
              AND comment_type NOT IN ( 'pingback' )
          ),
           wp_posts.post_date  ) as mcomment_date
        from
           $wpdb->posts wp_posts
        where
           post_type = 'post'
           and post_status = 'publish'
           and comment_count > '0'
        order by
   mcomment_date desc  limit $limit";

iguanarama · Accepted Answer · 2016年12月31日 16:08

The wp_comments table has a field 'comment_type' which indicates pingback etc. So while my SQL knowledge is not great, I think you just need one extra line in your query where you're selecting from the comments table:

[...]
where
    wpc.comment_post_id = wp_posts.id
    AND wpc.comment_type != 'pingback'
    [...]

exclude pingbacks from wordpress SQL query

About