2
\$\begingroup\$

I have a table containing URLs as they should appear in English and equivalent translations of them into other languages. Originally, I was told that there would always be an English translation, so when listing them in the application that updates them I just searched for the ones where iso = 'GB'. However, it turns out that it's entirely possible for there not to be an English translation.

I needed to update my query for generating the list displayed in the application so that it would select the English translation in preference to any other translations that might exist, but if there is no English translation, then use a single example of which other translations there are present (doesn't matter which, just as long as there's a single entry presented to the user).

The query I came up with is as follows. I'd appreciate it if you could give it a sanity check so I can be confident that it's returning correct results. (It appears to do so with the current data set, but it's currently quite a small set).

SELECT `t`.* 
FROM (
    SELECT `translations_urls`.* 
    FROM `translations_urls` 
    ORDER BY iso = 'GB' DESC, 
    `order` ASC, 
    `url_gb` ASC
) AS `t` 
GROUP BY `url_gb`

The table schems is as follows:

CREATE TABLE `translations_urls` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `order` INT(10) NOT NULL COMMENT 'Sort order',
    `iso` CHAR(2) NOT NULL COMMENT 'ISO country code',
    `url_gb` VARCHAR(127) NOT NULL COMMENT 'Original URL',
    `url_trans` VARCHAR(127) NOT NULL COMMENT 'Translated URL',
    `controller` VARCHAR(127) NOT NULL COMMENT 'Controller the URL refers to',
    `action` VARCHAR(127) NOT NULL COMMENT 'Action the URL refers to',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `iso_url_gb` (`iso`, `url_gb`),
    INDEX `url_trans` (`url_trans`),
    CONSTRAINT `fk_translations_urls_translations_countries_iso` FOREIGN KEY (`iso`) REFERENCES `translations_countries` (`iso`)
)
COMMENT='URL translations'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=303;
\$\endgroup\$

1 Answer 1

2
\$\begingroup\$

Your query isn't right, and in my opinion, your schema doesn't make sense either.

What is the purpose of the sub-select in your query? Is your query not equivalent to

SELECT * 
    FROM `translations_urls` AS `t`
    GROUP BY `url_gb`
    ORDER BY iso = 'GB' DESC, `order` ASC, `url_gb` ASC;

Furthermore, what do you intend to achieve with GROUP BY? You aren't using any aggregate functions in your output columns. In fact, with a sane database engine (e.g. PostgreSQL), such a query would generate an error like

ERROR: column "t.id" must appear in the GROUP BY clause or be used in an aggregate function


Your schema seems awkward to me, for several reasons:

  • The url_gb column implies that GB is the original language, which, as you stated, is no longer a valid assumption.
  • The use of url_gb to identify a translation's original results in a normalization problem. If the URL of a UK-English document changes, you have to update the url_gb of all of its translations.

Furthermore, translations should have language codes, not country codes. (An exception would be if you were "translating" legal contracts, in which case you want a different document per country.)

One solution is to make a self-referential table that could form a tree of documents:

CREATE TABLE `documents`
( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
, `order` INT(10) NOT NULL COMMENT 'Sort order'
, `iso` VARCHAR(6) NOT NULL COMMENT 'ISO language code'
, `url` VARCHAR(127) NOT NULL COMMENT 'URL'
, `controller` VARCHAR(127) NOT NULL COMMENT 'Controller the URL refers to'
, `action` VARCHAR(127) NOT NULL COMMENT 'Action the URL refers to'
, `translated_from` INT(10) UNSIGNED COMMENT 'id of document from which this was translated'
, PRIMARY KEY (`id`)
, UNIQUE INDEX `iso_url` (`iso`, `url`)
, FOREIGN KEY (`iso`) REFERENCES `translations_languages` (`iso`)
, FOREIGN KEY (`translated_from`) REFERENCES `documents` (`id`)
)
ENGINE=InnoDB;

Another approach, which I prefer, is to distinguish between documents and their translations. The advantage of this is that you can nominate one of the translations as the canonical original, to prevent recursion, which is harder to deal with in SQL.

CREATE TABLE `documents`
( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
, `order` INT(10) NOT NULL COMMENT 'Sort order'
, `canonical` INT(10) UNSIGNED COMMENT 'Canonical (original) translation'
, `controller` VARCHAR(127) NOT NULL COMMENT 'Controller the URL refers to'
, `action` VARCHAR(127) NOT NULL COMMENT 'Action the URL refers to'
, PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

CREATE TABLE `translations`
( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
, `document_id` INT(10) UNSIGNED NOT NULL
, `iso` VARCHAR(6) NOT NULL COMMENT 'ISO language code'
, `url` VARCHAR(127) NOT NULL COMMENT 'URL'
, PRIMARY KEY (`id`)
, UNIQUE INDEX `document_id_iso` (`document_id`, `iso`)
, UNIQUE INDEX `url` (`url`)
, FOREIGN KEY (`iso`) REFERENCES `translations_languages` (`iso`)
)
ENGINE=InnoDB;

ALTER TABLE `documents`
    ADD FOREIGN KEY (`canonical`) REFERENCES `translations` (`id`);

An annoyance with this schema is the mutual foreign key relationship. To add a document, you would first have to insert a document with NULL as the canonical translation, then insert the first translation, then update the document with the id of the first translation.


With the latter schema, a query to preferentially return English versions, falling back to a canonical text in another language, would be:

SELECT *
    FROM `documents` AS `d`
        INNER JOIN `translations` AS `t`
            ON `t`.`document_id` = `d`.`id`
    WHERE
        `iso` = 'en' OR
        (`iso` <> 'en' AND `t`.`id` = `d`.`canonical`);
\$\endgroup\$
1
  • \$\begingroup\$ Thanks for the answer, but I don't remember any of the details of this anymore, and it's now moot anyway as I don't work at that particular place any longer. I didn't expect to see anybody bothering to leave an answer on a question asked nearly 2 years ago! \$\endgroup\$
    – GordonM
    Commented Aug 20, 2013 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.