How do I get a value from a composite key table using a Rails model?

Question

I have the following schema (* means primary key):

languages
  id*
  english_name
  native_name

native_to_target_language
  native_language_id*
  target_language_id*
  target_language_name
  overview_text

(target_language_name is the name of the target language as written in the native language).

I want to get the value of target_language_name from the native_to_target_language table given values for native_language_id and target_language_id.

What is the best way to go about getting this? Use Composite Primary Keys from http://compositekeys.rubyforge.org/? Is there a standard way WITHOUT using a raw SQL query?

Answer 1

It sounds like you might want something like Polymorphic Associations. This would require a separate table per language, which is (I think) what you are discussing.

However, it seems like there might be a better solution to your problem. Particularly, you might be able to come up with a better schema to solve this (unless the database is already in use and you are creating a Rails app to try to access it).

Can you describe the overall problem you are attempting to solve?

Answer 2

Its not very clear if you need CRUD operations. If you want to find then you can do the following:

NativeToTargetLanguage.find(:all, :conditions => {
        :native_language_id => native_language_id,
        :target_language_id => target_language_id }
)

Answer 3

Instead of rolling your own translation system, have you investigated any "off the shelf" varieties?

For example there's Globalize which does a lot of this for you.

Having a table with a compound key that represents a connection from one record in a table to another is going to be so much trouble. Generally you need to maintain an A<->B association as a pair of A->B and B->A varieties.

What about this as a general example:

class Language < ActiveRecord::Base
  belongs_to :phrase
  has_many :translations,
    :through => :phrase
end

class Phrase < ActiveRecord::Base
  has_many :translations
end

class Translation < ActiveRecord::Base
  belongs_to :language
  belongs_to :phrase
end

In this case Phrase is a kind of record representing the term to be translated. It could represent "French" or "English" or "Click Here" depending on the circumstances.

Using this structure it is straightforward to find the proper term to describe a language in any language you have defined.

For example, roughly:

<%= link_to(Language.find_by_code('fr').phrase.translation.find_by_language_id(session_language_id), '/fr') %>