Hi I’m trying to work out the best way of managing image files uploaded to my site so I can clean out the ones I don’t want in due course. But that’s not really what I want to understand…
Background:
My site allows users to create a profile for pets. My logic is to upload an image then capture a set of data points about the pet. I then save both image and attributes to my table as a Thing. I believe this stores a reference to the image not the image itself.
The problem is I can’t easily tell if the image that has been uploaded has been used or not.
So I’ve created a new Table called Pet Images that I populate as soon as an image is uploaded. I capture the image, file URL, file size etc. to that table, plus I have a field to reference the Pet that I populate if the user clicks ‘Save’.
I intend to create a scheduled job to delete uploaded Images that are not associated with Pets.
I’m OK with all of this so far.
The problem is what to do with the images I’ve already uploaded before I started doing this.
This is where I need help…
I want to update my Pet Images table with details of all images already uploaded, then
I want to update my Pet Images table with the Pets that have a link to each thing in my Pet Images table.
I know how to do this in SQL, just not in Bubble. Something like:
insert image into pet_images select image from file_manager f where not exists (select image from pet_images p where p.image = f.image)
update pet_images pi set pet = (select pet from pets p where p.image = pi.image)
Does this make sense? Is this possible or do I need to export and reimport the data after processing it outside of Bubble? Is there a way of accessing the set of image files already uploaded?