mysql - SQL 将 "non-existing"行添加到基于所有变体的结果中并删除重复项

Question

我正在尝试添加虚拟(不存在)行以获取系统中所有可用翻译的列表

我想要得到的是如下:(示例1)

id     title     source_langcode    target_langcode |    (type)----------------------------------------------------|...                                                 |205   Iriure EN         en               en         | ⟵ original205   Iriure EN         en               de         | ⟵ non translated (virtual)205   IriUKure          en               uk         | ⟵ translation...                                                 |

What I'm having now:(example 2)

id     title     source_langcode    target_langcode |  ----------------------------------------------------|...                                                 |205   Iriure EN         en               en         | ⟵ original                205   IriUKure          uk          DYMMY_LANGCODE  | ⟵ translation205   Iriure EN         en               uk         | ⟵ this is redundant205   Iriure EN         en               de         | ⟵ non translated (virtual)...

Problem:
translations table stores only translated & original entities, but I need to add translations also for non translated entities, and this “virtual” records (their fields) should be based on source entity fields.

Overview:
There are two tables in this system

languages - list of all available languages in the system (example 3)

id     langcode----------------1        en2        de3        uk

translations - list of source entities and their translations based on source entity (example 4)

id     title        langcode    default_langcode   created    changed   ...   ...---------------------------------------------------------------------------------...205     Iriure EN      en            1            xxxxxxxxxx xxxxxxxxxx205     IriUKure       uk            0            xxxxxxxxxx xxxxxxxxxx206     UK Nunc UK     uk            1            xxxxxxxxxx xxxxxxxxxx207     LucidusDE      de            1            xxxxxxxxxx xxxxxxxxxx...

To indicate that current row is a source default_langcode (int) column is used, in other case default_langcode => 0 means that this is a translation

Tried solutions:
One of the solution was using cross join on languages table with default_langcode = 1 only rows but in this case I got fields from source entity on translated ones.I've used:

SELECT 
  translations.id AS id, 
  translations.title,
  translations.langcode AS source, 
  translations.created AS created,
  l.langcode,
  tr.langs as translations_langs, 
  IF(FIND_IN_SET(l.langcode, tr.langs) AND translations.langcode <> l.langcode, 1, 0) as is_translation
FROM 
  translations translations

CROSS JOIN languages l

INNER JOIN (
  SELECT 
    translations.id, 
    GROUP_CONCAT(translations.langcode separator ',') as langs, 
    COUNT(translations.langcode) as items
  FROM translations translations 
  GROUP BY translations.id
) AS tr ON translations.id = tr.id

WHERE node_field_data.default_langcode = '1' 

ORDER BY node_field_data.id

我尝试使用UNION的另一个解决方案，但在这种情况下，我得到的结果类似于示例1，其中包含冗余行。

查询:

(SELECT 
  translations.id AS id, 
  translations.title,
  translations.langcode AS source_language, 
  l.langcode as target_language,
  translations.created AS created

FROM 
  translations translations

CROSS JOIN languages l

WHERE 
  translations.default_langcode = '1' 
)

UNION ALL

(SELECT 
  translations.id AS id, 
  translations.title,
  translations.langcode AS source_language,
  'DYMMY_LANGCODE' as target_language ,
  translations.created AS created

FROM 
  translations translations

WHERE 
  translations.default_langcode = '0' 
)

ORDER BY id

摘要:我需要的只是获取所有语言和每种翻译语言代码之间的差异(其中 default_langcode = 0)，并从源实体(其中default_langcode = 1)

就我个人而言，由于系统特定的原因，我宁愿不要使用联合，但如果这是唯一的方法，那么就可以了。理想情况下，我想使用带有连接的 translations 表。

协议(protocol):
行 ~ 实体 ~ 记录

感谢您的帮助!

Answer 1

试试这个(我没有运行，所以可能是语法错误)

SELECT t1.Id,
ISNULL(t.title,t1.title+'_'+t1.langcode) title,
t1.source_langcode,
t1.langcode target_langcode,
CASE
WHEN t1.default_langcode = t.langcode THEN '(original)'
WHEN t.langcode IS NULL THEN '(non translated)'
ELSE '(translation)'
END
FROM
(
--GET AllCodes trim suffix
SELECT t.Id,t.title,l.langcode,t.source_language
(SELECT Id,LEFT(title, LEN(title)-3) title, langcode source_language
FROM translations
WHERE default_langcode = 1
) t
JOIN lanquages ON 1=1
) t1
LEFT JOIN translations t ON t1.Id = t.Id AND t1.langcode = t.langcode