SQL: Join table with itself to get default values

user_14763591 0
Viewed 86

I am sure this or a similar question has been raised (and answered) before here but as I'm not really able to summarize the problem in one sentence I could not find a related post.

I've created a sample schema to explain my problem:

CREATE TABLE `term` (
  `tid` int(10) unsigned NOT NULL,
  `langcode` varchar(12) CHARACTER SET ascii NOT NULL,
  `name` varchar(255) NOT NULL,
  `weight` int(11) NOT NULL,
  `default_langcode` tinyint(4) NOT NULL,
  `content_translation_source` varchar(12) CHARACTER SET ascii DEFAULT NULL,
  PRIMARY KEY (`tid`, `langcode`)
);

CREATE TABLE `term__parent` (
  `tid` int(10) unsigned NOT NULL,
  `langcode` varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '',
  `parent_id` int(10) unsigned NOT NULL,
   PRIMARY KEY (`tid`,`langcode`)
);

/* Add term data. */
INSERT INTO `term` VALUES 
(1, 'en', 'Channel 1', 0, 1, 'und'),
(2, 'en', 'Channel 2', 0, 1, 'und'),
(3, 'en', 'Channel 3', 6, 1, 'und'),
(4, 'en', 'Channel 4', 4, 1, 'und'),
(5, 'en', 'Channel 5', 0, 1, 'und'),
(6, 'en', 'Channel 6', 1, 1, 'und'),
(7, 'en', 'Channel 7', 0, 1, 'und'),
(1, 'de', 'Kanal 1', 1, 0, 'en'),
(2, 'de', 'Kanal 2', 0, 0, 'en'),
(3, 'de', 'Kanal 3', 0, 0, 'en'),
(7, 'de', 'Kanal 7', 0, 0, 'en'),
(3, 'xx-lolspeak', 'Channal 3', 6, 0, 'en'),
(8, 'xx-lolspeak', 'Channal 8', 6, 1, 'und');

/* Add hierarchy data. */
INSERT INTO `term__parent` VALUES 
(1, 'en', 0),
(2, 'en', 1),
(3, 'en', 0),
(4, 'en', 0),
(5, 'en', 4),
(6, 'en', 4),
(7, 'en', 6),
(1, 'de', 0),
(2, 'de', 1),
(3, 'de', 0),
(7, 'de', 3),
(3, 'xx-lolspeak', 0),
(8, 'xx-lolspeak', 0);

https://www.db-fiddle.com/f/eawNennfaEaZwaDhqtFjBA/9

There are two tables: term and term__parent. term__parent defines relationships between the items in term.

What I'm trying to get

The data shown in the example is (simply spoken) content that is partially translated. I try to get a list of all content in a specified language. If there is no translation available, the content should be printed with its default language. The contents default language is not fixed, it can be (in this example) one of "en", "de" or "xx-lolspeak" and could be translated into the other languages.

Within each language, the hierarchy ("parent_id" and "weight") may be different, so data from term__parent needs to be printed also.

The result of the query I'm searching for should look like this (based on the data defined in the fiddle) when filtering for the langcode "en":

tid langcode name weight parent_id
1 en Channel 1 0 0
2 en Channel 2 0 1
3 en Channel 3 6 0
4 en Channel 4 4 0
5 en Channel 5 0 4
6 en Channel 6 1 4
7 en Channel 7 0 6
8 xx-lolspeak Channal 8 6 0

When filtering for the langcode "de":

tid langcode name weight parent_id
1 de Kanal 1 1 0
2 de Kanal 2 0 1
3 de Kanal 3 0 0
4 en Channel 4 0 0
5 en Channel 5 0 4
6 en Channel 6 0 4
7 de Kanal 7 0 3
8 xx-lolspeak Channal 8 6 0

When filtering for the langcode "xx-lolspeak":

tid langcode name weight parent_id
1 en Channel 1 0 0
2 en Channel 2 0 1
3 xx-lolspeak Channal 3 6 0
4 en Channel 4 4 0
5 en Channel 5 0 4
6 en Channel 6 1 4
7 en Channel 7 0 6
8 xx-lolspeak Channal 8 6 0

I've played with some self joins and subqueries but couldn't get the correct results. Any ideas how to achieve this?

mysql
2 Answers

For example:

SELECT DISTINCT COALESCE(y.tid,x.tid) tid  
              , COALESCE(y.langcode,x.langcode) langcode
              , COALESCE(y.name,x.name) name
              , COALESCE(y.weight,x.weight) weight
              , p.parent_id
           FROM term x 
           LEFT 
           JOIN term y 
             ON y.tid = x.tid 
            AND y.langcode = 'en' 
            AND y.default_langcode = 1
           JOIN term__parent p
             ON p.tid = COALESCE(y.tid,x.tid)
            AND p.langcode = COALESCE(y.langcode,x.langcode);
user_15296730

I finally found a solution to get the correct results. The query (is very ugly and) surely could be optimized performance-wise. But it works :)

SELECT 
    DISTINCT t.*
    , p.parent_id
FROM
    term t
INNER JOIN 
    (
        SELECT 
            tid  
            , langcode
        FROM
            term t_i1
        WHERE
            langcode = 'de'
        UNION
        SELECT 
            tid  
            , langcode
        FROM
            term t_i2
        WHERE
            default_langcode = 1
    ) t_i
    ON t.tid = t_i.tid AND t.langcode = t_i.langcode
INNER JOIN
    term__parent p
    ON p.tid = t.tid AND p.langcode = t.langcode
GROUP BY
    t.tid
ORDER BY
    t.weight
    , t.name

With this query I am now able to get the translated und untranslated terms plus their weights and parent. See https://www.db-fiddle.com/f/m7bmuiDwowg3UQYixDnY96/10

user_147635910
Related