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

Foxing

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'

Foxing

Answer 2

Check your cdn-strategry to match on both systems, or set it explicit.

.env (SHOPWARE_CDN_STRATEGY_DEFAULT) or in file config/packages/shopware.yml (shopware.cdn.strategy)

This can be id, filename, physical_filename or plain.

I don't like the current behaviour, too. See also: https://github.com/shopware/proposal/issues/50

Moeller

Where exactly are the (product) image files stored? If it is in a folder, please name the folder because I can't find it, if it is in the database, where and how was it done. Thanks – Otha

Check: public/media/ There is no dedicated folder on disc for product-images. – Moeller

Answer 3

There is the option, that filenames can be broken. When you copied filepaths between different systems you can have encoding issues. See the table here Filesystem encoding and PHP.

been there, done that.

You can identify these issues when you iterate over files and convert the names to something where you can see byte differences. This is important as md5 will look onto the texts how it is stored rather how it is printed.

Orchidaceous

Answer 4

In addition to the approved answer, Shopware replaces ad with g0. This was introduced to avoid ad-blocker to block images. However, this is what worked for me.

SELECT lower(hex(id)), 
  file_name,
  concat(
    'media/',
    CONCAT(
        IF(SUBSTRING(md5(lower(hex(id))), 1, 2) = 'ad', 'g0', SUBSTRING(md5(lower(hex(id))), 1, 2)), '/',
        IF(SUBSTRING(md5(lower(hex(id))), 3, 2) = 'ad', 'g0', SUBSTRING(md5(lower(hex(id))), 3, 2)), '/',
        IF(SUBSTRING(md5(lower(hex(id))), 5, 2) = 'ad', 'g0', SUBSTRING(md5(lower(hex(id))), 5, 2)), '/'
    ),
    IFNULL(concat(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), ''),
    file_name, '.', file_extension
  ) as path
FROM media
WHERE file_name LIKE "%<file_name>%"

References:

1. https://github.com/shopware/shopware/blob/efe7b8ae224c1ef5601c43ff465a9dd5908aa8d3/src/Core/Content/Media/Pathname/PathnameStrategy/AbstractPathNameStrategy.php#L14
2. https://issues.shopware.com/issues/SW-12910

Slothful

Answer 5

I had to get the path from files/media. In addition I had to set the right TIMEZONE, because mySQL and PHP werent using the same. So this worked for me:

SET time_zone='UTC';
SELECT 
  CONCAT(
    'files/media/',
    CONCAT(
        IF(SUBSTRING(md5((CONCAT(CONCAT(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), file_name) ) ), 1, 2) = 'ad', 'g0', SUBSTRING(md5((CONCAT(CONCAT(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), file_name))), 1, 2)), '/',
        IF(SUBSTRING(md5((CONCAT(CONCAT(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), file_name))), 3, 2) = 'ad', 'g0', SUBSTRING(md5((CONCAT(CONCAT(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), file_name))), 3, 2)), '/',
        IF(SUBSTRING(md5((CONCAT(CONCAT(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), file_name))), 5, 2) = 'ad', 'g0', SUBSTRING(md5((CONCAT(CONCAT(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), file_name))), 5, 2)), '/'
    ),
    IFNULL(CONCAT(FLOOR(UNIX_TIMESTAMP(uploaded_at)), '/'), ''),
    file_name, '.', file_extension
  ) as path
FROM media
WHERE file_name='somefilename';

Barrios