Translate words from one database with other database translation

Question

I have two databases, one database containing all my pictures like:

Database 1 name: images
-----+----------------------------+----------------------+-----------+------------+------------
| id | description                | tags                 | imagename | resolution | location  |
-----+----------------------------+----------------------+-----------+------------+------------
| 1  | We standing at eiffeltower | france, green, blue  | IMG01.JPG | 1280x1024  | /img/2020 |
| 2  | We standing at bridge      | france, orange, grey | IMG02.JPG | 1280x1024  | /img/2020 |

Database 2 name tagTranslations (for Dutch translation)
-----+--------+-----------------------
| id | tag    | translation          |
-----+--------+-----------------------
| 1  | france | frankrijk            |
| 2  | orange | oranje.              |
| 3  | grey   | grijs.               |
| 4  | green  | groen                |
| 5  | blue   | blauw                |

Now i want with 1 mysql query to get a result like this:
"We standing at eiffeltower", "france, green, blue", "IMG01.JPG", "1280x1024", "/img/2020", "frankrijk", "groen", "blauw"

"We standing at bridge", "france, orange, grey", "IMG02.JPG", "1280x1024", "/img/2020", "frankrijk", "oranje", "grijs"

will `tags` column always have three tags in it? – zealous Jun 08 '20 at 21:47 — zealous, Jun 08 '20 at 21:47
No, some have 3 tags, some have 10 tags or more. – Rob van den Eijnden Jun 08 '20 at 22:10 — Rob van den Eijnden, Jun 08 '20 at 22:10

GMB · Accepted Answer · 2020-06-08T22:02:15.693

You first effort should go into fixing your data modeL Each image tag should be stored in a separate table, on a different row. Storing delimited lists in database columns in the root of many evils, as you are starting to see. More about this can be read in this famous SO post.

That said, you could use a corelated subquery with find_in_set() and group_concat():

 select
    i.id,
    i.description,
    (
        select group_concat(
            tt.translation 
            order by find_in_set(tt.tag, replace(i.tags, ', ', ','))
            separator ', ' 
        )
        from tagTranslations tt
        where find_in_set(tt.tag, replace(i.tags, ', ', ','))
    ) tags,
    i.imagename,
    i.resolution,
    i.location
from images i

The correlated subquery retrieves rows from the translation table whose tag can be found in the tags list of the corresponding images row. For this, we use handly MySQL function find_in_set() (we need to remove the space after the comma for the function to work properly); then, aggregation function group_concat() regenerates a delimited list of translations, using find_in_set() again to honor the original order of tags.

Demo on DB Fiddle:

id | description                | tags                     | imagename | resolution | location 
-: | :------------------------- | :----------------------- | :-------- | :--------- | :--------
 1 | We standing at eiffeltower | frankrijk, groen, blauw  | IMG01.JPG | 1280x1024  | /img/2020
 2 | We standing at bridge      | frankrijk, oranje, grijs | IMG02.JPG | 1280x1024  | /img/2020

That is what i was looking for, and yes you are right. The data model is something i should work on. I haven't done this before because the first database is a pretty old one and the translation came later to my mind to get this implemented. Thanks a lot. Kudo's for your answer... — Rob van den Eijnden, Jun 08 '20 at 22:09

score 0 · Answer 2 · edited Jun 10 '20 at 10:53

0

Try out the code below:

CREATE VIEW table_comb AS
   SELECT * FROM images
   UNION ALL
   SELECT * FROM tagTranslations

edited Jun 10 '20 at 10:53

David Buck

3,752
35
31
35

answered Jun 10 '20 at 10:29

Maria Israel Sathyan

1
1

Translate words from one database with other database translation

2 Answers2