How to list the uploaded files (image file) of unsaved nodes?

Question

From the question What happens if a user uploads an image but doesn't save the node? and its answer, there is a table called file_managed and for any uploaded file (an image in my case), it sets 0 in status column, and if the image node is saved, it is set to 1. During each cron run, all files with 0 status will be removed.

I am using bulk_file_nodes module to upload the images. Unfortunately, this module sets the status 1 for uploaded files and no matter if their node is saved or not.

I need to accomplish what Drupal core does for unsaved nodes of uploaded files. To do so I have to list those files. There are about 30000 images and about 2500 nodes in my website that are uploaded by people and it is growing very fast every day.

The brute force method is to check the entire fid column of field_data_field_image table of the database for every file (in fact for every fid of file_managed) to see if that file is attached to a node or not, I think it is not a good solution at all.

Is there any way else to handle this?

thanks

Answer 1

The short answer is that there isn't really a way to do this.

The longer version is that you could make some SQL to find all candidates. The issue is that you can see if a file is used somewhere (on entites) on the file_usage table, but some files wont be "used in that manor. This means that finding a file "not being used" is not the same as it's safe to delete it.

A solution could be to make a cron job, that only checks images that has just been uploaded, but you still have the issue that you might end up deleting files that shouldn't have been deleted.

Answer 2

If the image field which contains image of the node type is field_data_field_image, the following query returns unassigned uploaded files to nodes:

<?php 
    $result = db_select("file_managed","fmanage")
        ->fields("fmanage",array('filename'))
        ->fields("fmanage",array('uid'))
            ->fields("fmanage",array('fid'))->execute();
    foreach ($result as $row){
        $fid = $row->fid;
        $result1 = db_select("field_data_field_image","imagefid")
            ->condition("imagefid.field_image_fid",$fid,"=")
        ->fields("imagefid",array('field_image_fid'))->execute();
        if($result1->rowCount() == 0){
            print $row->filename."</br>";
            print $row->uid."</br>";
            print "</br>";
        }
    }
?>

Just notice that, the field_data_field_image must be the only image field in the website. If there is other image field with another name, the query should search for them too. Because I use the field_data_field_image for every image field in my website, I only need to search this table.

I executed the above code and returned the correct result.