database query trouble

Hello, I'm creating a multilingual blog, where a translator would
translate single posts into several languages. I'm having some trouble
searching and comparing records.

My database is set up like this:
Posts: id, body, original_id, language_id
Languages: id, name

A single post, translated into several languages is grouped by the
number in 'original_id', like this:

http://www.harryvermeulen.nl/database.jpg

I'm looking for a way to find all the posts in (for example) english,
that do not have a translation in (for example) japanese yet.

I hope I'm making sense.

Kind regards,
Harry

It might work out a lot better for original posts to have an original_id
of NULL.

Then posts with no translation can be identified as follows:

SELECT *
  FROM posts
WHERE original_id IS NULL
   AND (SELECT count(*) FROM posts WHERE original_id = id) = 0

That's how I would do it, because I like it when NULL means "you've
asked a stupid question". And in my mind, "What was the original post
for this original post" is a stupid question. :slight_smile:

However, the easiest is probably just to add a translations_count column
to the posts table and use a before_save filter to update it. I
believe this would be justifiable denormalization, because it would
reduce your "untranslated posts" query to

SELECT *
  FROM posts
WHERE translations_count = 0

Ciao,
Sheldon.