0

I am using wordpress to syndicate RSS feeds and I get a lot of duplicates, however most duplicate plugins only check for duplicated post title. This is no good as posts often have the same title but are in fact different items, such as job offers "music producer"

I want to check for duplicate posts using the permalink meta. The RSS plugin Feedwordpress swaps the wordpress permalink for the rss article url, and this defines duplicates more than the title..

so here is the SQL code I found for checking duplicate titles. how can I modify it to check for duplicate posts via their permalinks? permalink is just a meta called "syndication_permalink"

help appreciated

select bad_rows.ID, bad_rows.post_title, post_type
                    from wp_posts as bad_rows
                    inner join (
                    select post_title,id, MIN(id) as save_this_post_id
                    from wp_posts
                    WHERE (
                    `post_status` = 'publish'
                    AND
                    `post_type` in ("post")
                    )
                    group by post_title
                    having count(*) > 1
                    ) as good_rows on good_rows.post_title = bad_rows.post_title
                    and good_rows.save_this_post_id <> bad_rows.id
                    and (bad_rows.post_status='publish' OR bad_rows.post_status='draft')
                    order by post_title,id;

*UPDATE

I managed to find code that selects the UNIQUE posts via meta that is not duplicated. So how can I 'reverse select' this code below?

SELECT id,meta_value
FROM wp_posts
LEFT JOIN wp_postmeta c ON ( wp_posts.ID = c.post_id )
WHERE post_type = 'post' AND meta_key = 'syndication_permalink' 
GROUP BY meta_value
HAVING Count(meta_value) > 1

how can I select the duplicate freelancer.com posts?

  post_id   meta_key                meta_value
  --------  -------------------     ----------------------------------------
  1         syndication_permalink   https://www.freelancer.com/projects/
  2         syndication_permalink   https://www.freelancer.com/projects/
  3         syndication_permalink   https://www.freelancer.com/projects/
  4         syndication_permalink   https://www.simplyhired.com/job/W6sVJ1
  5         syndication_permalink   https://www.mandy.com/uk/job/576913/junior

I actually want to change their status to draft

*UPDATE I ended up modifying some other code and somehow got this working. I had had to hack together other bits of code. But it works. However, I need to convert them to 'draft' status. Can't seem to figure out how to put an UPDATE on a SELECT query now

select bad_rows.ID, bad_rows.post_title, post_type, meta_value
                    from wp_posts as bad_rows
                    LEFT JOIN wp_postmeta c ON ( bad_rows.ID = c.post_id )
                    inner join (
                    select post_title,id, MIN(id) as save_this_post_id
                    from wp_posts
                    inner JOIN wp_postmeta c ON ( wp_posts.ID = c.post_id )
                    WHERE (
                    `post_status` = 'publish'
                    AND meta_key = 'syndication_permalink' 
                    )   
                    group by meta_value
                    having count(*) > 1
                    ) as good_rows on good_rows.post_title = bad_rows.post_title
                    and good_rows.save_this_post_id <> bad_rows.id
                    and (bad_rows.post_status='publish' AND meta_key = 'syndication_permalink' )
                    order by post_title,id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.