Complex sorting on MySQL database

Question

I'm facing the following situation.

We've got an CMS with an entity with translations. These translations are stored in a different table with a one-to-many relationship. For example newsarticles and newsarticle_translations. The amount of available languages is dynamically determined by the same CMS.

When entering a new newsarticle the editor is required to enter at least one translation, which one of the available languages he chooses is up to him.

In the newsarticle overview in our CMS we would like to show a column with the (translated) article title, but since none of the languages are mandatory (one of them is mandatory but i don't know which one) i don't really know how to construct my mysql query to select a title for each newsarticle, regardless of the entered language.

And to make it all a little harder, our manager asked for the possibilty to also be able to sort on title, so fetching the translations in a separate query is ruled out as far as i know.

Anyone has an idea on how to solve this in the most efficient way?

Here are my table schema's it it might help

> desc news;
+-----------------+----------------+------+-----+-------------------+----------------+
| Field           | Type           | Null | Key | Default           | Extra          |
+-----------------+----------------+------+-----+-------------------+----------------+
| id              | int(10)        | NO   | PRI | NULL              | auto_increment |
| category_id     | int(1)         | YES  |     | NULL              |                |
| created         | timestamp      | NO   |     | CURRENT_TIMESTAMP |                |
| user_id         | int(10)        | YES  |     | NULL              |                |
+-----------------+----------------+------+-----+-------------------+----------------+


> desc news_translations;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| enabled         | tinyint(1)       | NO   |     | 0       |                |
| news_id         | int(1) unsigned  | NO   |     | NULL    |                |
| title           | varchar(255)     | NO   |     |         |                |
| summary         | text             | YES  |     | NULL    |                |
| body            | text             | NO   |     | NULL    |                |
| language        | varchar(2)       | NO   |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

PS: i've though about subqueries and coalesce() solutions but those seem rather dirty tricks, wondering if something better is know that i'm not thinking of?

Answer 1

This is not a fast approach, but I think it gives you what you want.
Let me know how it works, and we can work on speed next :)

select nt.title
  from news n 
  join news_translations nt on(n.id = nt.news_id)
 where nt.title is not null
   and nt.language = (
          select max(x.language)
            from news_translations x
           where x.title is not null
             and x.new_id = nt.news_id)
 order 
    by nt.title;