CODEWITHSUNDEEP
phpmysqlsqlmultilingual
olivier
olivier

Reputation: 1017

Multilingual MySQL content : how to select a given language or another if the one specified isn't available?

I'm developing a multilingual website PHP and would like to get content in a given language if available and in another one if not. I will try to explain my problem below. If something is not clear, please let me know.

My tables :

First case :

→ Easy to implement with a JOIN.

Second case :

→ How to implement that ? Is it possible in a single request ?

Some notes :

Your help will be really appreciated ! Thank you in advance !

Kind regards,

olivier

Upvotes: 3

Views: 2006

Answers (2)

bobflux
bobflux

Reputation: 11581

This solution works for many rows but you need 1 LEFT JOIN per language, and the order of the JOIns specifies priority.

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)

(Note : I suppose here that $1 and $2 are the user's first 2 preferred languages, and they are cached in the session, so there is no need for extra JOINs with l10n).

With your table structure this is the only meaningful way to set the language order. You would need an extra table to specify each user's language preferences instead of storing the order in l10n table. So let's suppose you have a table

user_l10n( user_id, l10n_id, order )

And let's suppose the table l10n keeps its "order" field, as a default.

If you do this :

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)

You'll get all the documents that match, and also the user-specified (or default) ordering, so the application can easily sort this out.

Now the idea is to avoid retrieving from the database all the rows that are in languages that are "shadowed" by an existing translation in a language that the user prefers.

The natural way to do this is a GROUP BY, but MySQL doesn't have an aggregate function that would work here...

You could do a dependent subquery (for title and description) ; this would be OK to grab one row, but horribly slow if you want to grab many rows.

But you can also do something else ! That relies on some shady behaviour of MySQL's non-standard GROUP BY clause...

First, gather a list of "content_id"'s that you want to display (result of a paginated search query, whatever). Then you can could do something like the horror that follows :

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)

Upvotes: 4

Sagi
Sagi

Reputation: 8011

this should give you the basis for the second case:

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

About performance, you need an index in content.content_id, content_l10n.content_id, l10n.l10n_id and l10n.order.

Upvotes: 1

Related Questions