Dublicate rows for translator in mysql-CodePudding

The site is translated into different by taking text from the table. Then the program gets into the database, translates and fills in the table, where there is an empty translation. The program takes only the text where there is no translation. In order to be translated into all languages you need to copy the string with an empty translation in different languages and then the program will translate.

In MYSQL table appers not translated rows for translator

select text,`from`,`to` from Translation where tran=''

I need prepare rows in other languages to same table

Something like this

insert into Translation (text,`from`,`to`)
select text, `from`, `to`
from Translation
where `to` not in ('en','zh','ar','th')
  and `to` != `from`
  and tran = ''

How to do it?

CodePudding user response：

This is something to fiddle around with. It's completely untested. Just to give the general idea for a pattern to impute missing values, or create placeholder records, from a seed set.

WITH 
  all_language_codes as
  ( select distinct
           `from`
      from translations
  ),
  words_which_have_no_translation_in_any_language as
  ( select text,
           `from`
      from translations t1
     where not exists
             ( select 1
                 from translations t2
                where t1.text = t2.text
                  and coalesce(trim(t2.tran),'') <> ''
             )
  )
select wwt.text,
       wwt.from,
       alc.from as to,
       '' as tran
  from words_which_have_no_translation_in_any_language wwt
 cross
  join all_language_codes alc
 where wwt.`from` <> alc.`from`