eZ Platform Discussions

Best way to locate users without content for deletion


#1

Hi

We have a large legacy installation that is upgraded from 5.3.3 to ez Platform 2.1 with legacy bridge. We have about 7000 ezUsers in the database and I’m in the process of trying to clean up the users base.

Is there any way to safely locate users with no content that can safely be deleted without creating orphan content?


#2

Probably not with the public API, but an SQL query with a left join with ezcontentobject table will give you wanted results.


#3

Yes, I’ve beein thinking about the same think. But is ezcontentobject checking enough? I’ce checked all the tables for a userid an come up with the following:

SELECT *
FROM ezuser
WHERE contentobject_id NOT IN (SELECT DISTINCT owner_id FROM ezcontentobject)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezcontentobject_version)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcontentobject_version)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ez_ct_change_entry)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ez_ct_change_log)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcollab_group)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezcollab_item)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcollab_item_group_link)
AND contentobject_id NOT IN (SELECT DISTINCT participant_id FROM ezcollab_item_message_link)
AND contentobject_id NOT IN (SELECT DISTINCT participant_id FROM ezcollab_item_participant_link)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcollab_item_status)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcollab_notification_rule)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcollab_profile)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezcollab_simple_message)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcomment)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcomment_subscriber)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcomment_subscription)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcontentbrowsebookmark)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezcontentbrowserecent)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezcontentclass)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezcontentclassgroup)
AND contentobject_id NOT IN (SELECT DISTINCT modifier_id FROM ezcontentclassgroup)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezforgot_password)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezgeneral_digest_user_settings)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezinfocollection)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezorder)
AND contentobject_id NOT IN (SELECT DISTINCT modifier_id FROM ezorder_status_history)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezpdf_export)
AND contentobject_id NOT IN (SELECT DISTINCT modifier_id FROM ezpdf_export)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezpreferences)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezprest_authcode)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezprest_authorized_clients)
AND contentobject_id NOT IN (SELECT DISTINCT owner_id FROM ezprest_clients)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezrss_export)
AND contentobject_id NOT IN (SELECT DISTINCT modifier_id FROM ezrss_export)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezrss_import)
AND contentobject_id NOT IN (SELECT DISTINCT modifier_id FROM ezrss_import)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezscheduled_script)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezsession)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezsubtree_notification_rule)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezsurveyresult)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezwishlist)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezworkflow)
AND contentobject_id NOT IN (SELECT DISTINCT modifier_id FROM ezworkflow)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezworkflow_group)
AND contentobject_id NOT IN (SELECT DISTINCT modifier_id FROM ezworkflow_group)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM ezworkflow_process)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezx_ezpnet_installation)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezx_ezpnet_module_branch)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezx_ezpnet_module_inst)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezx_ezpnet_module_patch)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezx_ezpnet_mon_group)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezx_ezpnet_mon_item)
AND contentobject_id NOT IN (SELECT DISTINCT creator_id FROM ezx_ezpnet_patch)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM nxc_shb_workflow_program_assignment)
AND contentobject_id NOT IN (SELECT DISTINCT user_id FROM nxc_shb_workflow_supervisor_assignment);

It should perform what I’m looking for, shouldn’t it?


#4

Argh, I misunderstood the question, please disregard my previous answer.

As a rule of thumb, you should never delete eZ users from the database, preciselly because it is so complicated to find all relations in the eZ database, especially if some 3rd party packages add the links to the users.

But looking at your query, it should do what you want, providing that there are no other tables which contain the user ID.