Get all media library assets that are actually used in content

[nikhiltri]

Hello,

We've had our Twill instance running in production for several months, and our Media Library is getting clogged up with images that aren't actually referenced from any of our content. What is the best way to get a list of all media library assets that are actually used? Either via SQL, tinker, or the Admin UI?

Thanks!
nikhil

[ifox]

Hi @nikhiltri,

Quick answer right now but I'll provide some example code later today or in the week.

Any image that is not referenced in the mediables table can be deleted, which should be reflected on the UI by the presence of the Delete button in the media library sidebar. We're definitely thinking about providing a direct view of unused images in the media library, especially since it is possible to bulk delete adding a filter should be very helpful.

[nikhiltri]

Thanks, @ifox!

Here's the query I put together:

SELECT DISTINCT 
    m.uuid,
    m.alt_text,
    m.filename
FROM
    medias m,
    mediables mb
WHERE
    m.id = mb.media_id
ORDER BY
    m.uuid

But it currently returns ~5,000 records, roughly the same as the number of images that appear in the Media Library (but oddly not exactly the same number. The query returns ~800 less images than what's in the Media Library UI). I don't think we're actually using all 5,000 of these images in website content. I imagine we're actually using a small fraction of that number.

What I want is: all images in the Media Library that are directly referenced by content, either in the block editor or in media fields.

Thanks for your help!
nikhil

[nikhiltri]

Hey @ifox, I’m sure you have a lot on your plate! Just bumping this in case you have any quick insight.

[ifox]

Hey @nikhiltri, indeed! But we actually have a lot of very nice updates in the mix that I'm sure you're going to like a lot :)

I do have a quick insight. Soft deleted and deleted records are the issue here. The mediables table keeps referencing them which means you'd have to write a pretty complex manual SQL query to get this in one go considering the amount of tables you have. Eloquent should help though.

But in the end this should be on Twill to take care of at least when force deleted, as I assume some developers most likely setup a scheduled artisan command to clean them up.

A force delete action on the trashed records and an empty trash action would definitely be nice features for the CMS UI which I'm going to tackle as soon as possible unless someone else want to jump in :)

[nikhiltri]

Hey @ifox,

Circling back to this question, here's what we came up with. It looks at all models that we've configured to have media, and all blocks on all content, and pulls out the images associated with each. Of the 5,000 images we have in our media library, this command pulls out about 3,500. That still seems high, but perhaps that's accurate.

        $medias = [];
        collect([
            \App\Models\Article::class,
            \App\Models\Page::class,
            ...
            \A17\Twill\Models\Block::class,
        ])->map(function($model) {
            $table = with(new $model)->getTable();
            $query = $model::whereHas('medias');
            if (\Schema::hasColumn($table, 'published')) {
                $query->published();
            }
            if (\Schema::hasColumn($table, 'deleted_at')) {
                $query->whereNull('deleted_at');
            }
            return $query->with('medias');
        })->map(function($query) use (&$medias) {
            return $query->chunk(100, function($items) use (&$medias) {
                foreach ($items as $item) {
                    $medias[] = $item->medias->map(function($media) {
                        return [
                            (string) $media->id => $media->alt_text
                        ];
                    })->all();
                }
            });
        });

        // Combine all the medias together
        $medias = array_merge(...$medias);

        // Collapse the array while preserving numeric keys
        $medias = array_reduce($medias, function ($carry, $item) { return $carry + $item; }, []);

        // At this point, everything should be deduped, since it's keyed by id
        $out = [];

        // Prep for str_putcsv()
        foreach ($medias as $id => $alt_text) {
            $out[] = [
                'id' => $id,
                'alt_text' => $alt_text,
                // TODO: Add Imgix link!
            ];
        }

Thanks!
nikhil