I wonder what's the best way to make a translations system with the orm (to make it simple, for a very basic multilingual e-commerce).
Here are the tables :
CREATE TABLE IF NOT EXISTS `langs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`iso_code` char(2) NOT NULL,
PRIMARY KEY (`id`),
KEY `lang_iso_code` (`iso_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ean13` varchar(13) DEFAULT NULL,
`upc` varchar(12) DEFAULT NULL,
`quantity` int(10) NOT NULL DEFAULT '0',
`price` decimal(20,6) NOT NULL DEFAULT '0.000000',
`ecotax` decimal(17,6) NOT NULL DEFAULT '0.000000',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `products_langs` (
`product_id` int(10) unsigned NOT NULL,
`lang_id` int(10) unsigned NOT NULL,
`name` varchar(128) NOT NULL,
`description` text,
PRIMARY KEY (`product_id`,`lang_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"Many to Many" Relation cannot be used, because I have datas in the relationship table.
A relation "Has Many" on products with a relation "Belongs To" on `langs `works, but I have to set a useless ID field on `products_langs`, and "$products->langs" will be an array using the ID as a key, not the lang ID.
It would be great if we can also get a fallback language (if the content is not available in XXX lang, we take the English content). EAV containers could be a good idea, but I think MySQL queries will be more optimized with a "lang_id" field rather than a varchar field.
Techically, you can create a compound PK with both the product_id and lang_id (which is what you would do from a database normalisation point of view), but that could lead to issues with deleting related tables because sometimes the ORM wants to set an FK to NULL, which you don't want with a PK field (and which is why the ORM doesn't support FK == PK).
I have seen people using relation conditions to automatically filter queries on a set language, so your application doesn't have to deal with it. Fallback is more difficult, as you have to make sure your query selects the correct record if both are present.
Thank you Harro, you confirm what I thought :) Actually, my `products_langs` table has an "id" field and i'm using a "Has Many - Belongs To" relationship.
I'm using :
$product = Model_Product::query()->related('products_langs')->where('products_langs.lang_id', 'in', array(1,2)->get(); Where 1 is my main lang and 2 the fallback lang.
It works fine... but the data relation $product->products_langs is an array with the "id" of `products_langs` as key, I think you'll see the problem, it would be great if I can do this :
echo (isset($product->products_langs[$currentLangID]) && strlen($product->products_langs[$currentLangID]) ? $product->products_langs[$currentLangID] : $product->products_langs[$fallbackLangID]); // I already know that my fallback language line will exist
Do you know if I can change the key for the lang_id? I think I will break the cascade save if I modify the ORM in this way and I don't think it's an "easy" adaptation of the ORM.
You could move the where() clause of that query to the relation condition, if you always want to filter, and you have the language codes available globally. Problem with this setup is that you only want 1 record, and since the order of the result of a WHERE IN is undefined, you could either get 1 or 2 as first record. Which is something you don't want I think.
Ideally you would want to be able to define a has_one relation, with a table that has a compound PK, with one of the PK columns defined 'fixed' using a condition on the relation.
That way it would be completely transparent for your application. In the current ORM, that is quite difficult to implement, but it could be a feature request for v2 (add it at https://github.com/fuelphp/orm/issues if you want).
I didn't use the relation condition because I need unfiltered results in the BackOffice.
I think i will create a function that uses the "$product->products_langs" array and return the key for a given lang_id or null if the row doesn't exist. It's not perfect but it will do the job ;)
Donate
