Shopware 6 media file path from database/MySQL

Question

In the Shopware 6 database in table "media" there is no column for the file location. There is only file_name, file_extension, and file_size. But there is no file_path.

Shopware somehow must convert the information from database to a file location to generate a file path like 'public/media/8f/25/74/1653530151/242178_0569-1.jpg'.

How does Shopware generate the file path? And is there a way to generate the file path via MySQL query?

MySQL [dev]> select * from media where file_name='242178_0569-1'\G
*************************** 1. row ***************************
             id: 0191b9f8e7634a15a4e494d1206f797f
        user_id: NULL
media_folder_id: d798f70b69f047c68810c45744b43d6f
      mime_type: image/jpeg
 file_extension: jpg
      file_size: 2161002
      meta_data: {"hash": "7b082f5a1a79e2dd0f39a2c8c1062a1c", "type": 2, "width": 1300, "height": 1300}
      file_name: 242178_0569-1
     media_type: ...
  thumbnails_ro: ...
        private: 0
    uploaded_at: 2022-05-26 01:55:51.920
     created_at: 2022-05-26 01:55:51.401
     updated_at: 2022-05-26 01:55:54.428

Answer 1

TL;DR Version:

SELECT 
  concat(
    'public/media/',
    REGEXP_REPLACE(left(md5(lower(hex(id))), 6), '(..)', '$1/'),
    IFNULL(concat(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), ''),
    file_name, '.', file_extension
  ) as path
FROM media
WHERE file_name='242178_0569-1';

will give you the file path 'public/media/8f/25/74/1653522951/242178_0569-1.jpg'

---

Full Answer:

Shopware generates the media file path in source code in Shopware/Core/Content/Media/File/FileLoader.php. If you dig deeper and presuming you're using the default implementation you'll find Shopware/Core/Content/Media/Pathname/UrlGenerator.php:

public function getRelativeMediaUrl(MediaEntity $media): string
{
    $this->validateMedia($media);

    return $this->toPathString([
        'media',
        $this->pathnameStrategy->generatePathHash($media),
        $this->pathnameStrategy->generatePathCacheBuster($media),
        $this->pathnameStrategy->generatePhysicalFilename($media),
    ]);
}

Part 1: generatePathHash

The method generatePathHash uses the media ID, generates an md5 hash, uses the first 6 characters, and adds a slash on position 2 and 4.

From your example the id '0191b9f8e7634a15a4e494d1206f797f' would generate '8f/25/74'

Part 2: generatePathCacheBuster

This method only uses the uploaded_at, but printed as a timestamp. If it's null, no path would be added.

Part 3: generatePhysicalFilename

This is basically just the file_name and file_extension.

Final SQL:

SELECT lower(hex(id)), 
  file_name,
  concat(
    'public/media/',
    REGEXP_REPLACE(left(md5(lower(hex(id))), 6), '(..)', '$1/'),
    IFNULL(concat(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), ''),
    file_name, '.', file_extension
  ) as path
FROM media
WHERE file_name='242178_0569-1';

will give you the requested file path 'public/media/8f/25/74/1653522951/242178_0569-1.jpg'