0

How can I SELECT a table1 column value with multiple table2 row values as replaced?

So the detailed problem definition is below;

Post Table

ID   description
-------------------
1    <a href="http://example.com?attachment_id=10">File1</a>, <a href="http://example.com?attachment_id=11">File2</a>
2    <a href="http://example.com?attachment_id=12">File3</a>, <a href="http://example.com?attachment_id=13">File4</a>

Attachment Table

ID   postId    guid
10   1         http://example.com/file1.jpg
11   1         http://example.com/file2.jpg
12   2         http://example.com/file3.jpg
13   2         http://example.com/file4.jpg

I want to get the first row (id: 1) in Post Table with the values replaced as corresponding 2 records in Attachment Table (which is id: 10 and id: 11)

Query result should be as below:

Expected Query Result:

ID   description
-------------------
1    <a href="http://example.com/file1.jpg">File1</a>, <a href="http://example.com/file2.jpg">File2</a>
2    <a href="http://example.com/file3.jpg">File3</a>, <a href="http://example.com/file4.jpg">File4</a>

I tried following query but only first value in description is replaced.

SELECT 
    post.ID, 
    REPLACE(post.description, CONCAT('http://example.com?attachment_id=', attachment.ID), attachment.guid) AS description 
FROM post
JOIN attachment ON post.ID = attachment.postId
GROUP BY post.ID

Also, I tried with REGEXP_REPLACE variation but I couldn't achieve the expected result.

Note: This is not the real data model, the tables are given to simplify the problem definition. I am trying to migrate data from an old WordPress system to Drupal 9. So in reality the data is kept in same table (posts and attachments in regards to post_type) and I build query with self join. What I need actually is, the file addresses in content. So I should replace the attachment URLs with file URLs.

tewoos
  • 45
  • 2
  • 10
  • 2
    This is a *terrible* data model, you can build your desired results from just the *Attachment* table. Also confusing why you have a column containing URLs called *guid*? – Stu Dec 13 '21 at 10:49
  • please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and normalize your table and the result will be simple – nbk Dec 13 '21 at 10:51
  • You should fix your database design, don't serialize datas, this goes against the purpose of using a RDBMS. And don't store HTML in your table, your table is actually strongly coupled with the application, which shouldn't be the case, you may want to use in the future the same datas in other kinds of applications, which don't use HTML – Cid Dec 13 '21 at 10:52
  • Actually I tried to simplify my problem. So the real problem is; I am trying to migrate data from maybe a 10 years old WordPress version to a Drupal 9. It's enough for me to keep attachment urls in content in wordpress as the addresses to the file. So I am trying to replace the attachment urls in content with directly file addresses. I should mention about the migration, sorry. – tewoos Dec 13 '21 at 12:01

0 Answers0