Advice on mass category update. Does this code logic and plan look correct?

peregrine

I want move discussions to one distinct category based on criteria

must meet all these criteria.

• discussions has 0 comments
• discussion is not an announcement
• discussion is older than two days

the guts of the plugins are this. admin clicks button in dashboard that calls this routine.

Does anyone see an issue with this logic., or did I miss something if I do this. meaning can you just change the categorid to a different number this way and run the dba/counts and will this update all the necessary tables and linkages.

    $deldays = 2;

    SQL->Update('Discussion')
    ->Set('CategoryID',"70")
    ->Where(array('CommentCounts<' => "1",
                  'DateInserted <' => Gdn_Format::ToDateTime(strtotime($deldays),
                  'Announcement<' => "1"  ))
    ->Put();

 Redirect('dashboard/dba/counts');

essentially category id is changed in discussion table and the dba counts is run (theoretically to update counters.).

there are no file attachments in any discussions.

hgtonight

This sounds like a solid plan.

The only thing I don't like about it is it will execute a recalculation of all counts for the db rather than just the category related ones. I would just call the CategoryModel::Counts() method with all the column names (i.e. 'CountDiscussions', 'CountComments', 'LastDiscussionID', 'LastCommentID', and 'LastDateInserted').

peregrine

thx. hgtonight.

so the idea is viable updates everything that needs to be updated is updated correctly, and not corrupt things.

Correct?

just call the CategoryModel::Counts() method with all the column names (i.e. 'CountDiscussions', 'CountComments', 'LastDiscussionID', 'LastCommentID', and 'LastDateInserted').

not sure how to do this or do you mean run it 5 times with each column as parameter, wouldn't that be slower.

CategoryModel::Counts('CountDiscussions');
CategoryModel::Counts('CountComments');
etc.

peregrine

@linc you did essentially the same thing mass moved discussions to vanilla help cateogries. Do you see a problem with above? or have any tips.

Linc

Use /dba/counts afterward rather than coding it into the move.

When I did it here, I did it with naked SQL queries rather than a plugin. If this is a 1-off action you're doing, I'd suggest doing the same for this.

Run the SQL as a "select" first to see what rows will be effected. That is SOP internally when operating on a live database.

peregrine

@Linc said:
Use /dba/counts afterward rather than coding it into the move.

meaning make the admin that run that as an individual process (button click), after move id done.
or instead of a redirect.
or instead of the category model idea that hgtonight had.

When I did it here, I did it with naked SQL queries rather than a plugin. If this is a 1-off action you're doing, I'd suggest doing the same for this.

it will be a daily event for laymen. so I want to make it easy, if it won't foul things up.

Run the SQL as a "select" first to see what rows will be effected. That is SOP internally when operating on a live database.

i could add that. Verify. then click.

peregrine

On a second point.

I also want to delete discussions that have zero comments that are older than a certain time period.

I've created a list of discussion id's.

loop through list of discussion ID's that met above criteria.

executing this for each discussion id.

$DiscussionModel->Delete($DiscussionID)

and after wards run /dba/counts

What flag can I use to prevent the Log Table from getting all the deleted discussions?

in other words I want to delete the discussions, but not have the very same discussions added to the Log Table.

or would I have to purge log table afterwards. it would be easy to skip the addition of adding to log table in the first place.

Anyone see an issue or problem with the above technique of have comments on this

Linc

Most of my comments addressed this as if you were trying to do this as a 1-off ultimately, sorry for the confusion.

Maybe reuse the methods invoked in /dba/counts instead of making your own. That's my only other suggestion.

hgtonight

Ah, if you are doing this as a type of manual cron job that will also affect other counts, don't listen to my previous post.

peregrine

thanks for the insights. both of you.

peregrine

I had to change a few column names in my original code.

But the above seemed to work as desired on a small database with the redirect.

I was more concerned if just changing category id field in the discussion table and then running the dba/counts covered all the bases, and it seems to. If anyone knows otherwise let me know.

Two clicks by an admin - didn't seem to onerous to me.