Geek Answers Handbook

Comprehensive sorting by MySQL database

I came across the following situation.

We have a CMS with an entity with translations. These translations are stored in another table with a one-to-many relationship. For example newsarticlesand newsarticle_translations. The number of available is languagesdynamically determined by the same CMS.

When entering a new newsletter, the editor needs to enter at least one translation, which one of the languages ​​available to him depends on him.

In the news review in our CMS, we would like to show a column with the (translated) title of the article, but since none of the languages ​​is required (one of them is required, but I don’t know which one), I don’t really know how to build my mysql query is to select a headline for each news, regardless of the language entered.

And to make all this a little more complicated, our manager asked for the opportunity to also be able to sort the title, so the translation selection in a separate request is excluded, as far as I know.

Does anyone have an idea how to solve this in the most efficient way?

Here is my table layout, this could 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 mean coalesce () subqueries and solutions, but they seem pretty dirty tricks, wondering if something is better known, which I don’t think about?

+5
2

, , , .
, , :)

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;
+1

, , , "" ? ...

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`

(ru), , (jp) (de) , "" , .

+1

More articles:


All Articles