SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multilingual content with fallback language

    Hi,

    I'm working on the database design of a multilingual website. I want to use the following popular method:

    Code:
    table products
    id
    price
    
    table products_i18n
    id
    product_id
    lang
    name
    description
    I've used this in the past and know it works fine and results in a nicely normalized database.
    An example query would be:

    Code:
    SELECT p.id, pi.name FROM products AS p
    INNER JOIN products_i18n AS pi ON pi.product_id = p.id
    WHERE pi.lang = 'EN'
    ------------------------
    1. screwdriver
    2. hammer
    3. nailgun
    The problem I'm facing now, is that I want to use a default language. In other words, if there's no content for the product in the given language, show the English name.
    If I were to query the list of products in Dutch, but "nailgun" was never translated, the above query would only give me two products:

    Code:
    SELECT p.id, pi.name FROM products AS p
    INNER JOIN products_i18n AS pi ON pi.product_id = p.id
    WHERE pi.lang = 'NL'
    ------------------------
    1. schroevendraaier
    2. hamer
    I would like the English "nailgun" to show up instead, but I'm having trouble with the joins that would make this work. I know I can detect the missing content in my application code and run a second query, but I'd rather not.

    Thanks very much in advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT p.id
         , COALESCE(pi_lang.name,pi_dflt.name) AS name 
      FROM products AS p
    LEFT OUTER 
      JOIN products_i18n AS pi_dflt 
        ON pi_dflt.product_id = p.id
       AND pi_dflt.lang = 'EN'
    LEFT OUTER 
      JOIN products_i18n AS pi_lang 
        ON pi_lang.product_id = p.id
       AND pi_lang.lang = 'NL'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is perfect! Thanks so much.
    If I had known it was that easy...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yup, the COALESCE is the key ingredient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •