MG 2.2 How to extract the media_id from data_id and hash?

[rdn]

From the direct link of the video?
Like:
/data/xfmg/video/1/data_id-xxxxhashxxxxxxxxxxxxxxxxxxxxxxxx.mp4
/data/xfmg/video/2/data_id-xxxxhashxxxxxxxxxxxxxxxxxxxxxxxx.mp4

Is it possible?
Is there a query I can run out of it?

The data_id from xf_attachment_data table seems to not point to the Media ID.

 

[rdn]

Using this query isn't always correct with video titles having been changed already (not using the file name by default).

SELECT * FROM `xf_mg_media_item` WHERE `xf_mg_media_item`.`title` = "filename_taken_from_xf_attachment_data"

 

[Chris D]

There can be multiple xf_attachment records for each xf_attachment_data record.

SELECT *
FROM xf_attachment
WHERE data_id = X

That gets you all attachments for the given data ID.

Why might there be more than one attachment? Attachment mirroring would cause that. The data is uploaded and stored once, but the attachment is a reference to it. There would be one attachment record for the post and one attachment record for the media item.

That's currently the only scenario where there would be more than one attachment but you can't rely on it for that not to change. For example if there was an add-on or a feature in XF in the future that would allow you to reuse attachments across anywhere, then there could be theoretically infinite numbers of attachments pointing to the same attachment data.

As it stands right now, to filter this down to the specific media item, you would find the attachments based on data_id by adding the content_type in there too:

SELECT *
FROM xf_attachment
WHERE data_id = X AND content_type = 'xfmg_media'

This should only return a single record but, as I said, you can't rely on it in future. Once you have that record with the xfmg_media content type, the content_id will be the media ID of the attachment data.

SELECT content_id
FROM xf_attachment
WHERE data_id = X AND content_type = 'xfmg_media'

 

[rdn]

Problem solved, thanks a lot Chris.