Equivalent of if/then/else accross rows?

[rekudos]

Hi,

Looking for a guru to help solve a tricky problem... I am storing objects in one table, and their translations in another with something like the following schema:

CREATE TABLE nodes (
  id              INTEGER        NOT NULL PRIMARY KEY,
  path            VARCHAR(255)   NOT NULL UNIQUE,
)

CREATE TABLE nodes_t (
  id              INTEGER     NOT NULL PRIMARY KEY,
  transof         INTEGER     NOT NULL REFERENCES nodes(id),
  src             INTEGER     NOT NULL,
  lang            VARCHAR(8)  NOT NULL,
  title           VARCHAR(255) NOT NULL,

There is always at least one translation (row) in nodes_t for each row in nodes. The first translation has src=1, the rest have src=0.

I want to retrieve the object and its translation, but if the translation for the specified language doesn't exist then I want the original or 'src' row.

For retrieving a single node I can do this by ORDERing by src and LIMITing to 1 row:

  select
    nodes.id,
    nodes.path,
    nodest.lang,
    nodest.title
  from nodes left join nodes_t
  on nodes.id == nodest.transof
  where
    lang='de' or src=1 AND nodes.id=1
  order by src
  limit 1;

However how do I join multiple nodes with their translations, where the translation is the language requested (if it exists) or the src row otherwise? In psuedo code it is something like this:

  id|transof|src|lang |title
  1 |1      |1  |en   |Home
  2 |1      |0  |de   |Hause
  3 |2      |1  |en   |About

  Give me the translations for nodes 1 and 2:

  if lang == 'de' then
     return rows 2 and 3
  else if lang == 'en' then
     return rows 1 and 3
  endif

I have looked at all kinds of combinations of INTERSECT, UNION, EXCEPT, EXISTS, IN etc and can't find a clean way. Ie, one that doesn't involve doing multiple queries. I think using DISTINCT ON(transof) would work but databases like SQLite don't support that.

Any ideas on how best to do this?

Cheers,
Mark.

 

[r937]

select nodes.id
     , nodes.path
     , coalesce(nodes_t.lang,nodes_t_src1.lang) as lang
     , coalesce(nodes_t.title,nodes_t_src1.title) as title
  from nodes 
inner 
  join nodes_t_src1
    on nodes_t_src1.transof = nodes.id
   and nodes_t_src1.src = 1
left outer
  join nodes_t
    on nodes_t.transof = nodes.id
   and nodes_t.lang = 'de'
 where nodes.id = 1

r937.com | rudy.ca

 

[rekudos]

Hi r937,

Thanks for the (amazingly!) quick response. I was thinking somebody might respond in 24 hours if I was lucky

I've never seen the coalesce function before - looks quite useful. In plain English, you are suggesting creating a third table to store the src/original translation?

If that is so, then is the check for the value of the src column still necessary, provided that it is not NULL?

select nodes.id
     , nodes.path
     , coalesce(nodes_t.lang,nodes_t_src1.lang) as lang
     , coalesce(nodes_t.title,nodes_t_src1.title) as title
     , coalesce(nodes_t.src,nodes_t_src1.src) as src
  from nodes
inner
  join nodes_t_src1
    on nodes_t_src1.transof = nodes.id
   and nodes_t_src1.src = 1   <---- not needed?
left outer
  join nodes_t
    on nodes_t.transof = nodes.id
   and nodes_t.lang = 'de'

Thanks again,
Mark.

 

[r937]

no, not a third table

the query joins to the nodes_t table twice

it would help if i didn't screw up the syntax!! :blush:

select nodes.id
     , nodes.path
     , coalesce(nodes_t.lang,nodes_t_src1.lang) as lang
     , coalesce(nodes_t.title,nodes_t_src1.title) as title
  from nodes 
inner 
  join nodes_t [b]as nodes_t_src1[/b]
    on nodes_t_src1.transof = nodes.id
   and nodes_t_src1.src = 1
left outer
  join nodes_t
    on nodes_t.transof = nodes.id
   and nodes_t.lang = 'de'
 where nodes.id = 1

r937.com | rudy.ca

 

[rekudos]

That's ok, I've been mixing up my left and my right today as well...

Actually, what about storing the original language in the nodes table, and just the translations in the nodes_t table. Then perhaps the following would be more efficient:

select nodes.id
     , nodes.path
     , coalesce(nodes_t.lang,nodes.lang) as lang
     , coalesce(nodes_t.title,nodes.title) as title
  from nodes
left outer
  join nodes_t
    on nodes_t.transof = nodes.id
   and nodes_t.lang = 'de'
 where nodes.id = 1

 

[r937]

that's an even better idea

r937.com | rudy.ca