• Welcome to the Chevereto user community!

    Here users from all over the world gather around to learn the latest about Chevereto and contribute with ideas to improve the software.

    Please keep in mind:

    • 😌 This community is user driven. Be polite with other users.
    • 👉 Is required to purchase a Chevereto license to participate in this community (doesn't apply to Pre-sales).
    • 💸 Purchase a Pro Subscription to get access to active software support and faster ticket response times.

Purge guest images older than specific date with SQL query

mkerala

👽 Chevereto Freak
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.

Code:
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.
 
Last edited:
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)
 
While this is an SQL query, will it actually delete the actual images on the filesystem?
 
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).
 
Back
Top