Geek asks and answers

MySQL multilingual content: how to choose a given language or another if the specified is not available?

I am developing a multilingual PHP website and want to receive content in this language if it is available and the other in another. I will try to explain my problem below. If something is unclear, let me know.

My tables:

  • content: content_id, url,date
  • content_l10n: content_id, l10n_id, title,description
  • l10n: l10n_id, name,order

The first case:

  • My visitor speaks french.
  • The content I want to display is available in English and French.
  • The website should display French content.

→ Easy to implement with JOIN.

Second case:

  • My visitor speaks french.
  • The content I want to display is only available in English .
  • The website should display English content.

→ ? ?

:

  • .
  • , , .
  • , .
  • .
  • content ( , ).
  • title , description NULL. , , .
  • (: , : l10n, order ASC).

! !

,

+3
2

, 1 LEFT JOIN , JOIns .

SELECT   c.url, c.date, 
         COALESCE( c1.title, c2.title ),
         COALESCE( c1.description, c2.description )
FROM      content c
LEFT JOIN content_l10n c1 ON (c1.content_id = c.content_id AND c1.l10n_id=$1)
LEFT JOIN content_l10n c2 ON (c2.content_id = c.content_id AND c2.l10n_id=$2)

(: , $1 $2 , , JOIN l10n).

. , l10n. , ,

user_l10n( user_id, l10n_id, order )

, l10n "" .

:

SELECT   ..., COALESCE(ul.order,l.order) AS order
FROM      
          content      c
JOIN      content_l10n cl USING (content_id)
JOIN      l10n         l  USING (l10n_id)                -- get default language order
LEFT JOIN user_l10n    ul ON    (ul.l10n_id=l.l10n_id    -- get user preferences if available
                                 AND ul.user_id=$user_id)
WHERE search condition on content, etc
ORDER BY content_id, COALESCE(ul.order,l.order)

, , ( ) , .

, , , "" , .

GROUP BY, MySQL , ...

( ); , , , .

- ! GROUP BY MySQL...

"content_id", ( ). - :

SELECT * FROM
(
    SELECT content_id, title FROM 
    (
        SELECT    c.content_id, c.title
        FROM      
                  content      c
        JOIN      content_l10n cl USING (content_id)
        JOIN      l10n         l  USING (l10n_id)
        LEFT JOIN user_l10n    ul ON    (ul.l10n_id=l.l10n_id AND ul.user_id=$user_id)
        WHERE cl.content_id IN ($list) AND c.title IS NOT NULL
        ORDER BY content_id, COALESCE(ul.order,l.order)
    ) d GROUP BY content_id
) t
JOIN
(
    SELECT content_id, description FROM 
    (
        SELECT    c.content_id, c.description
        FROM      
                  content      c
        JOIN      content_l10n cl USING (content_id)
        JOIN      l10n         l  USING (l10n_id)
        LEFT JOIN user_l10n    ul ON    (ul.l10n_id=l.l10n_id AND ul.user_id=$user_id)
        WHERE cl.content_id IN ($list) AND c.description IS NOT NULL
        ORDER BY content_id, COALESCE(ul.order,l.order)
    ) d GROUP BY content_id
)
USING (content_id)
+4

:

SELECT   content.url, content.date, content_l10n.title, content_l10n.description
FROM     content, content_l10n, l10n
WHERE    content.content_id = content_l10n.content_id AND
         content_l10n.l10n_id = l10n.l10n_id AND
         content.content_id = {$contentId}
ORDER BY l10n.order ASC
LIMIT    1

content.content_id, content_l10n.content_id, l10n.l10n_id l10n.order.

+1

More articles:


All Articles