# Database query trouble

**URL:** <https://www.ruby-forum.com/t/database-query-trouble/139605>\
**Category:** Rails\
**Created:** [June 5, 2008, 11:27am UTC](https://www.ruby-forum.com/t/database-query-trouble/139605 "2008-06-05T11:27:11Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![harryvermeulen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/eb9ed0/32.png) [@harryvermeulen](https://www.ruby-forum.com/u/harryvermeulen)\
**Post date:** [June 5, 2008, 11:27am UTC](https://www.ruby-forum.com/t/database-query-trouble/139605/1 "2008-06-05T11:27:11Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![harryvermeulen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/eb9ed0/32.png) [@harryvermeulen](https://www.ruby-forum.com/u/harryvermeulen)\
**Post date:** [June 5, 2008, 12:16pm UTC](https://www.ruby-forum.com/t/database-query-trouble/139605/2 "2008-06-05T12:16:57Z")

</div>

-----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](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-----