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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
-----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----- --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---