Help needed clearing out blank entries from members listing

Hi,

Having had an onslaught of spam registrations I have no choice but to work through the database to do a bulk delete.

I did :

delete from elgg_entities where guid=<guid from elgg_user_entities>

delete from elgg_entity_relationships where guid_one=<guid from elgg_user_entities

delete from elgg_users_entity where banned='yes';

update elgg_entities set enabled="no" where owner_guid=<guid from elgg_user_entities> or container_guid=<guid from elgg_user_entities>;

 

However my Members on the public site are still populated by many blank pages where the original spammers used to be listed.

Ideas welcome.

  • Idea: don't do that!

    By doing that you're leaving untold numbers of unattached metadata/annotations/private_settings/relationships etc.

     

    Restore from your backup

    You need to use the elgg api - create a run-once script to iterate through the users using whatever criteria you decided to detect spammers and use the proper delete() method

  • Is it too difficult to tell me what query is used to retrieve the Members list ?  That's all I'm asking for.  

    Getting back a list of members shouldn't be a horrendously complex query, and if it is, well that brings me back to my point on the other thread.  ;-)

     

  • Getting a list of users and deleting a set of users are two completely different operations.

    The $user->delete() function triggers multiple other delete queries throughout Elgg. So deleting a user includes in fact multiple separate delete statements. I strongly recommend using the API to get and delete the users as Matt said.

  • which would be along the lines of:

    $users = elgg_get_entities(array('type' => 'user', 'limit' => 10, 'blah' => 'blah')); // define your paramaters for identifying users here.

    foreach ($users as $user) // iterate through the list of users you have retrieved

    {
    $user->delete(); // delete the currently examined user entity.

    }

  • Yep, use the code ura soul provided.

    Do you have some specific parameters in mind how to tell apart spammers from regular users?

  • I listed my sql in the wrong order.... big hint was "delete from elgg_users_entity where banned='yes';"

    They were already banned .... I just wanted to clear out the database.     Given the choice between (a) wasting time figuring out the API and then going through a cycle of writing and debugging scripts  or (b) a few minutes of SQL .... (b) seemed vastly more attractive.  

    The fact this can't be done easily in SQL says a lot for the poor database design of Elgg.  With its ghastly generic data model. If you don't understand why Generic Data Models are so bad, just look here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056 .... Tom's first answer to the question which starts "Here is a excerpt from my forthcoming book where I talk about this (and show you how ugly, hard and inefficient queries against your very flexible model will be)" .  Sure that answer was given on an Oracle website about Oracle Database, but the answer applies to Mysql, Postgres or any relational database that you choose to abuse with a generic data model.

    I'm not alone in my dislike of Elgg, medium-long term I fully intend to migrate away from the platform, because I agree with the negative opinions outlined here http://stackoverflow.com/questions/1564096/how-flexible-is-elgg and elsewhere on the internet.

    Elgg is something I unfortunatley inherited and fully intend to disown as soon as I can.   I wanted to like Elgg, but the more I dig, the more I look at it, the more I can't wait to rid my servers of its presence.