Purge guest images older than specific date with SQL query

[mkerala]

If you are looking to reclaim some space on your server by deleting those guest images without any views, run the below query to force expire them in database which will get deleted cleanly.

update chev_images set image_expiration_date_gmt='2018-11-10 00:00:00' WHERE image_date <= '2017-11-14 00:00:00' and image_user_id is NULL and image_views <=500;

image_expiration_date_gmt='2018-11-10 00:00:00': Enter a date in past
image_user_id is NULL: Delete only guest images
image_date <= '2017-11-14 00:00:00' : Till which date images should be deleted
image_views <=500 : Avoid deleting images with greater than 500 views

No matter how many images are there, the system will slowly and cleanly remove them in batches without causing much load on the system. Both database entry and image file will get deleted.

 

[Rodolfo]

Hey, this is very clever stuff! I will add it for sure.

 

[mkerala]

Thanks 🙂

 

[twm]

MARK

 

[imghut]

This is a really cool idea, is it still valid?

 

[twm]

This is a really cool idea, is it still valid?

error

 

[imghut]

error

Your posts have a perplexing brevity to them 🤣

 

[schubert24]

Little mod to make it dynamic:

UPDATE `chv_images`
SET `image_expiration_date_gmt` = Date(Date_Sub(Now(), INTERVAL 1 DAY))
WHERE `image_date` <= Date(Date_Sub(Now(), INTERVAL 12 MONTH))
AND `image_user_id` is NULL
AND `image_views` <= 1;

I'll add this to my housekeeping bot (see my other post)

 

[rdn]

I will add it for sure.

Please 🙂

 

[Rodolfo]

Please 🙂

yeah it could be added to dashboard tools, very simply stuff like determine the interval, the affected range and then images wiped.

 

[hkmix]

It only work for guess ?

 

[mkerala]

It only work for guess ?

Remove "and image_user_id is NULL" to make it work for everyone.

 

[sirfpsycho]

how to do this? where do I set these values and run?

 

[starkinternet]

yeah it could be added to dashboard tools, very simply stuff like determine the interval, the affected range and then images wiped.

Sir. Does v4 have this yet?

 

[Rodolfo]

how to do this? where do I set these values and run?

You need to query the database directly. Either with database software or using the repl.

 

[Rodolfo]

Sir. Does v4 have this yet?

No, I've created a RFC for this. Vote here: https://chevereto.com/community/thr...oving-guest-images-within-a-date-range.14641/

 

[Andy]

While this is an SQL query, will it actually delete the actual images on the filesystem?

 

[Rodolfo]

While this is an SQL query, will it actually delete the actual images on the filesystem?

A database query won't ever touch the filesystem, if you care to read it says that it queues the images for deletion, which later happens via background job (cron).