CODEWITHSUNDEEP
mysqlsqldatabase
akasummer
akasummer

Reputation: 362

Select translation for given string

I am beginner in SQL and trying to design a query. I use my database for storing translations of different strings (which are used in UIs of different products).

My database contains 4 tables:

"lang_id" is FK references "id" from "Language" table

"string_1" and "string_2" are FKs that reference "id" from "String" table.

All my tables are using InnoDB engine, except Copy table which uses MyISAM engine and contains fulltext index on "name" column. I have triggers to ensure that IDs of strings in "String" and "Copy" tables match.

Use case:

User wants to translate new strings (which are not in DB yet) from english to German. User wants to find a reference translations for a string "Login" in order to reuse it and keep consistency.

User submits data (for example using a form on the website):

What user wants to get:

All strings from DB, which contain word "Login" and their corresponding German translation.

What I have so far: ID of English language is 1, ID of German language is 2

SELECT * FROM String s 
JOIN (SELECT id FROM Copy 
      WHERE MATCH (name) AGAINST ('Login')) r 
ON s.id = r.id WHERE s.language = 1

That returns all strings in English, containing "Login".

I cannot figure what to do next, I think I should somehow join this result with "Translation" table.

Could someone guide me in the right direction?

P.S. I am using MySQL 5.5 (that's why I have this "Copy" table) and I understand that it might be better to use search engine for such tasks, but I want to accomplish it without one.

Thank you in advance.

EDIT

For example I have:

       String                         Translation               Language
|id| name     | lang_id |     |id| string_1 | string_2 |      |id| name |
-------------------------     --------------------------      -----------
| 1| Good     |    1    |     |1 |    1     |     2    |      | 1| En   |
| 2| Gut      |    2    |     |2 |    4     |     3    |      | 2| De   |          
| 3| Good day |    1    |
| 4| Guten Tag|    2    |

I search for "Good", input lang "En", output lang "De", want to get result:

| Good     | Gut       |
| Good day | Guten Tag |

EDIT

sqlfiddle

Upvotes: 1

Views: 1251

Answers (1)

Sotirca Mihaita George
Sotirca Mihaita George

Reputation: 238

select il.name, tl.name
from String il
join Translation t on (t.string_1 = il.id)
join String tl on (tl.id = t.string_2)
where (il.lang_id = [id_of_initial_lang]) and 
      (tl.lang_id = [id_of_translated_lang]) and 
      (il.name like '%[searchText]%')
union
select il.name, tl.name
from String il
join Translation t on (t.string_2 = il.id)
join String tl on (tl.id = t.string_1)
where (il.lang_id = [id_of_initial_lang]) and 
      (tl.lang_id = [id_of_translated_lang]) and 
      (il.name like '%[searchText]%')

Upvotes: 1

Related Questions