CODEWITHSUNDEEP
phpmysql
ChrisR
ChrisR

Reputation: 14447

Complex sorting on MySQL database

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?

Upvotes: 5

Views: 430

Answers (2)

Ronnis
Ronnis

Reputation: 12833

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;

Upvotes: 1

Jeff Parker
Jeff Parker

Reputation: 7507

Assuming I've read your problem aright, you want to get a list of titles for articles, preferring the "required" language? A query for that might go along the lines of ...

SELECT * FROM (
    SELECT nt.`title`, nt.news_id
    FROM news n
      INNER JOIN news_translations nt ON (n.id = nt.news_id)
    WHERE title != ''
    ORDER BY
        CASE
            WHEN nt.language = 'en' THEN 3
            WHEN nt.language = 'jp' THEN 2
            WHEN nt.language = 'de' THEN 1
            ELSE 0 END DESC
) AS t1
GROUP BY `news_id`

This example prefers a title in English (en) if available, Japanese (jp) as a second preference, and German (de) as a third, but will display the first 'other' entry if none of the requested languages are available.

Upvotes: 1

Related Questions