Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Custom SQL to select and delete duplicate listing for Sabai Directory plugin. Based on address AND phone number.
-- Select query
SELECT pst.`post_id`, pst.`post_title`, pst.`post_slug`
FROM (
SELECT min(pst.`post_id`) AS `min_id`, dst.`post_title`, cnt.`phone`, loc.`address`, loc.`street`, loc.`city`, loc.`state`, loc.`zip`, loc.`country`, COUNT(*) AS `cnt`
FROM (SELECT DISTINCT `post_title`
FROM `tableprefix_sabai_content_post`
WHERE `post_entity_bundle_type` = 'directory_listing' AND
`post_slug` REGEXP '[[:digit:]]') dst JOIN `tableprefix_sabai_content_post` pst
ON dst.`post_title` = pst.`post_title`
JOIN `tableprefix_sabai_entity_field_directory_contact` cnt
ON pst.`post_id` = cnt.`entity_id`
JOIN `tableprefix_sabai_entity_field_directory_location` loc
ON pst.`post_id` = loc.`entity_id`
GROUP BY dst.`post_title`, cnt.`phone`, loc.`address`, loc.`street`, loc.`city`, loc.`state`, loc.`zip`, loc.`country` HAVING `cnt` > 1
) DBL_CNT JOIN `tableprefix_sabai_content_post` pst
ON DBL_CNT.`post_title` = pst.`post_title` AND
DBL_CNT.`min_id` < pst.`post_id`
JOIN `tableprefix_sabai_entity_field_directory_contact` cnt
ON pst.`post_id` = cnt.`entity_id` AND
DBL_CNT.`phone` = cnt.`phone`
JOIN `tableprefix_sabai_entity_field_directory_location` loc
ON pst.`post_id` = loc.`entity_id` AND
DBL_CNT.`address` = loc.`address` AND
DBL_CNT.`street` = loc.`street` AND
DBL_CNT.`city` = loc.`city` AND
DBL_CNT.`state` = loc.`state` AND
DBL_CNT.`zip` = loc.`zip` AND
DBL_CNT.`country` = loc.`country`
ORDER BY pst.`post_slug` ASC;
-- Delete query
DELETE pst
FROM (
SELECT min(pst.`post_id`) AS `min_id`, dst.`post_title`, cnt.`phone`, loc.`address`, loc.`street`, loc.`city`, loc.`state`, loc.`zip`, loc.`country`, COUNT(*) AS `cnt`
FROM (SELECT DISTINCT `post_title`
FROM `tableprefix_sabai_content_post`
WHERE `post_entity_bundle_type` = 'directory_listing' AND
`post_slug` REGEXP '[[:digit:]]') dst JOIN `tableprefix_sabai_content_post` pst
ON dst.`post_title` = pst.`post_title`
JOIN `tableprefix_sabai_entity_field_directory_contact` cnt
ON pst.`post_id` = cnt.`entity_id`
JOIN `tableprefix_sabai_entity_field_directory_location` loc
ON pst.`post_id` = loc.`entity_id`
GROUP BY dst.`post_title`, cnt.`phone`, loc.`address`, loc.`street`, loc.`city`, loc.`state`, loc.`zip`, loc.`country` HAVING `cnt` > 1
) DBL_CNT JOIN `tableprefix_sabai_content_post` pst
ON DBL_CNT.`post_title` = pst.`post_title` AND
DBL_CNT.`min_id` < pst.`post_id`
JOIN `tableprefix_sabai_entity_field_directory_contact` cnt
ON pst.`post_id` = cnt.`entity_id` AND
DBL_CNT.`phone` = cnt.`phone`
JOIN `tableprefix_sabai_entity_field_directory_location` loc
ON pst.`post_id` = loc.`entity_id` AND
DBL_CNT.`address` = loc.`address` AND
DBL_CNT.`street` = loc.`street` AND
DBL_CNT.`city` = loc.`city` AND
DBL_CNT.`state` = loc.`state` AND
DBL_CNT.`zip` = loc.`zip` AND
DBL_CNT.`country` = loc.`country`;
Owner

sdynamo commented Jun 20, 2017

Used this code to clean-up my local business directory site: CityShoppingPoint
Select query at the top to check which rows are going to be deleted, and in the bottom is the delete query.
Replace tableprefix with your site's DB table prefix.
Feel free to use this script AT YOUR OWN RISK.
Backup first, I don't take any reponsibility from using the code.

Owner

sdynamo commented Jun 21, 2017

If you do such a directory site with Wordpress, or if you just need to remove hentry check this gist and put that in your theme's function.php
Or use Code Snippets plugin.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment