1

The Situation

I have two tables unit and unit_translations defined as:

CREATE TABLE `unit` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL COMMENT 'Title of unit',
  `description` longtext NOT NULL COMMENT 'Description of the unit',
  `idLevel` int(11) default NULL COMMENT 'Foreign key referencing level id',
  `position` smallint(6) NOT NULL COMMENT 'List sort order',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `title` (`title`),
) ENGINE=InnoDB;

Table has this data:

#id, title, description, idLevel, position
'1', 'Unit 001', 'Unit 001', '1', '1'

and

CREATE TABLE `unit_translations` (
  `id` int(11) NOT NULL auto_increment,
  `idUnit` int(11) default NULL COMMENT 'Foreign key referencing unit id',
  `locale` varchar(8) NOT NULL COMMENT 'Translation locale',
  `field` varchar(32) NOT NULL COMMENT 'Field in object being translated',
  `content` longtext COMMENT 'Translated value',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `lookup_unique_idx` (`locale`,`idUnit`,`field`),
  KEY `IDX_14213810232D562B` (`idUnit`),
  CONSTRAINT `FK_14213810232D562B` FOREIGN KEY (`idUnit`) REFERENCES `unit` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

That holds this data:

# id, idUnit, locale, field, content
'1', '1', 'en', 'title', 'A Day at the Beach'
'2', '1', 'en', 'description', 'How a typical day at the beach could be'

(Don't get caught up in the data it is silly right now)

The Task

Create a Select that selects everything from unit but replaces the content in unit.title and unit.description with the translated value from unit_translations. So the result would like like:

# id, title, description, idLevel, position
'1', 'A Day at the Beach', 'How a typical day at the beach could be', '1', '1'

I've managed to accomplish this with the following SQL:

SELECT u.id, trans1.trans as title, trans2.trans as description, u.idLevel, u.position
    FROM unit u
    JOIN (SELECT t.idUnit as trans_id, t.content as trans FROM unit_translations t JOIN unit u ON t.idUnit = u.id WHERE t.field = "title" AND t.locale ='es') AS trans1 ON u.id = trans1.trans_id
    JOIN (SELECT t.idUnit as trans_id, t.content as trans FROM unit_translations t JOIN unit u ON t.idUnit = u.id WHERE t.field = "description" AND t.locale ='es') AS trans2 ON u.id = trans2.trans_id

The Question:

Is there a build-in way to fetch the two rows/values form unit_translations and merge them into the one row from unit? Or is what I have come up with the only way to do this?

5
  • Why do you use such an old version of MySQL? 5.0 is EOL, long time ago. Commented Oct 21, 2014 at 21:25
  • Trust me If I could I would update that, but it is not up to me :-) Commented Oct 22, 2014 at 8:07
  • There are several other ways to write this query. Given the old version, I wouldn't use this (derived tables) but a query that joins 2 times to these tables. Commented Oct 22, 2014 at 9:52
  • Not sure I fully uuderstand what you mean or how? Commented Oct 22, 2014 at 10:18
  • I'll post an answer later. Commented Oct 22, 2014 at 10:24

1 Answer 1

0

It's not the only way to do it, but the other solutions won't perform any better. You could create a function and use that for the values of your column, which might make your query easier to read. But it won't perform better and depending on your scale, it may perform quite a bit worse.

1
  • Well that is both satisfying and a bit surprising. But thanks for your feedback! Commented Oct 22, 2014 at 8:08

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.