I have a site that deals exclusively with user generated content.

After a few years, the users table is getting to a stage where it can probably be pruned of folk who have registered but not actually submitted any content.

What query would I run to show the users who have zero posts?

I thought something like select id from craft_users where id not in (select distinct authorId from craft_entries) would do it.

The distinct query certainly returns 1020 rows where we actually have around 1800 users.


Looks like this will do the trick

select * from craft_users
left join craft_entries on craft_users.id = craft_entries.authorId
where craft_entries.authorId is null and craft_users.id <> "1"
  • 1
    You probably don't want to delete the results of that query from the craft_users table, though. Take the id from the results and delete the matching ids from the craft_elements table. The cascading deletes on the foreign keys will take of cleanup up the relevant rows in the related tables.
    – Brad Bell
    Nov 15 '17 at 19:05

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.