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
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thursday 05 June 2008 09:10:14 Harry Vermeulen wrote:
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.
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. 
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.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFIR7zgpGJX8XSgas0RAhVXAJ9nvZ+iom8CsPoS2JGNnLDGwySJ1wCgtZVk
hOrp+DXFCBC14JFnhuUGWis=
=N0Em
-----END PGP SIGNATURE-----
