Relation Model Issues,

Yii 1.1 General Discussion
1

The Four Tables:

languages

id … shortcode … name … locale

1 … en … English … English

2 … es … Spanish … Espanol

language_mapping

id … table_name … field_name

1 … item … name

2 … item … description

3 … item … price1label

4 … category … name

…

language_dictionary

id … record_id … language_mapping_id … language_id … value

1 … 3 … 2 … 1 … A Full bodied wine that costs $40

2 … 3 … 1 … 1 … Wine Name

3 … 3 … 1 … 2 … Vino Nombre

4 … 3 … 2 … 2 … Un vino de gran cuerpo, que cuesta $40

…

item

id … type … price1 … price2 …

1 … Wine … 10 … 20

2 … Wine … 20 … 30

3 … Wine … 30 … 40

…

The thing is, the item table can be any table that holds data that can be translated. There is a category table, a filter table, and I think a few others. They each have records that have fields that need to have a translation option.

language_mapping allows me to declare what tables/fields are allowed to be translated. This is for admin purposes so the admin who makes changes only adds translations to the fields I want them to be able to. This also allows me to dynamically add/remove tables and fields.

item -> language_mapping (in this case, where language_mapping.table_name = ‘item’)

… -> language_dictionary (based on fields available in language_mapping, limited by language_mapping.table_name = ‘item’ as above, which should be dynamic were the category table model be referencing language_dictionary)

… -> languages (on language_dictionary.language_id = languages.id)

I know exactly how the mysql would be structured, in short:


SELECT item.id, l_m.id, l_m.field_name, l.name, l_d.value

FROM item 

LEFT JOIN language_mapping AS l_m ON l_m.table_name = 'item' 

LEFT JOIN language_dictionary AS l_d ON l_d.record_id = item.id AND l_d.language_mapping_id = l_m.id 

LEFT JOIN languages AS l ON l.id = l_d.language_id 

WHERE item.id = 1

The result of that query in this case would be

item id … language mapping id … field name … language … value

1 … 1 … name … English … Wine Name

1 … 1 … name … Spanish … Vino Nombre

1 … 2 … description … English … A Full bodied wine that costs $40

1 … 2 … description … Spanish … Un vino de gran cuerpo, que cuesta $40

1 … 3 … price1label …NULL … NULL

From that data I would construct an array of items to access everything in PHP. I generally like running one query and let PHP process the data rather than running multiple queries. Either way… the end result would be an array or object:

array(

… item_id => array(

… ‘name’ => array(

… ‘en’ => ‘Wine’,

… ‘es’ => ‘Vino’

… )

… )

);

I’m really tired right now, so I am sure there are some grammatical and spelling errors. For the most part I am stuck with the listed DB structure but might have some flexibility to alter the structure to make it work with any suggestions. I have total freedom with the code however.

Thanks for any and all help!

2

Or I guess another question would be…

Item Model:


	public function relations()

	{

		return array(

			'languages' => array(self::HAS_MANY, 'LanguageMapping', '', 'on'=>"table_name='item'" ),

		);

	}

LanguageMapping Model:


	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'item' => array(self::BELONGS_TO, 'Item', '', 'on'=>"table_name='item'" ),

			'values' => array(self::HAS_MANY, 'LanguageDictionary', array('language_mapping_id'=>'id') ),

		);

	}

LanguageDictionary Model:


	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'mapping' => array(self::BELONGS_TO, 'LanguageMapping', array('id' => 'language_mapping_id') ),

		);

	}

Is there any way to limit the relationship between the LanguageMapping model and the LanguageDictionary model to only show dictionary entries limited by or grouped by the item.id in the first Model?

PSEUDO CODE:


'values' => array(self::HAS_MANY, 'LanguageDictionary', array('language_mapping_id'=>'id'), where record_id = item.id ),