I'm trying to create a settings table that takes into account entity types and languages. The database and Setting model structure is just below.
id: Primary key. Auto increment.
entity: The type of entity. This is usually the name of the MySQL table from another model (site, user, transaction).
entity_id: The ID (primary key) of the entity above.
language: The 2 letter language code (en, fr, es). Null if the variable is for all languages.
key: The key of the setting which is used to search.
value: This can be any type of data. It gets serialized upon save.
created_at: The timestamp when the record was created.
modified_at: The timestamp when the record was last modified.
The point of this table is to use if for any type of entity (user, site, transaction, etc) without the need for creating individual settings tables for each entity type. For example, I have a site table that holds customized sites for our clients. If I wanted them to have a custom titles in English and French for their site (let's say their site ID was 4545), I would add entries to the settings table above as follows:
$setting_en = \Model_Setting::forge();
$setting_en->entity = 'site';
$setting_en->entity_id = 4545;
$setting_en->language = 'en';
$setting_en->key = 'title';
$setting_en->value = "My site title in English";
$setting_en->save();
$setting_en = \Model_Setting::forge();
$setting_en->entity = 'site';
$setting_en->entity_id = 4545;
$setting_en->language = 'fr';
$setting_en->key = 'title';
$setting_en->value = "Titre de mon site en français";
$setting_en->save();
This would create 2 records in my settings table for my site entity with ID 4545. In my site model, I have a $_has_many relationship called settings which is structured as follows in the _init() method:
When I fetch a site object from the site model and dump the settings relationship using $site->settings, I see all of the setting objects. I also have a settings relationship in my user and transaction models (and other models too) which all print out the relationships perfectly fine. FuelPHP indexes the settings relationship array by the setting ID.
My problem comes into play when I need an easy way to access a specific setting by the key and language. For example, if I want to display the site title in French, the only way to currently do this is to have a custom method in each model that loops through the settings relationship objects and check if the key (title) and language (fr) are the ones I'm looking for, like so: $site->get_setting_value('title', 'fr'). THe method code is just below.
public function get_setting_value($key, $language) {
foreach ($this->settings as $setting) {
if ($setting->key === $key && $setting->language === $language) {
return $setting->value;
}
}
return null;
}
Since FuelPHP indexes the settings relationship by the setting ID, there's no simple way to do this. If my sites, users and transactions have 50-100 settings each, I can imagine the performance impact if the code has to loop through that method everytime it wants to find a specific setting from the relationship array.
Is there an easier way to make this type of settings structure work with ORM so that I can fetch it by the key/language without having to loop through the relationship array every time?
I want to avoid creating custom model relationships like 'title_en' and 'title_fr' because we are going to have tons of dynamic settings as the project progresses and it will be impossible to keep track of.
I want to avoid using EAV as it prevents us from using custom properties in our models. I also don't think EAV is the way to go for this type of setup.
Part of what I want to do here is call in all settings via a Fuel's ORM related('settings') method so that Fuel does an join onto the site object with a single database call.
If anyone has any insight on a way to make this happen, it would be greatly appreciated. Thanks.
Nobody is stopping you from defining compound PK's, there is no reason why you should have to use an auto-incremental ID column. That's just a default.
What you can do on the relation between a data Model and your entities Model is define a condition. So you can do things like:
protected static $_has_many = array(
'modelname' => array(
'model_to' => '\\Some\\Model\Name',
'key_from' => 'id',
'key_to' => 'modelname_id',
'conditions' => array(
'where' => array(array('column', '=', 'value'));,
'order_by' => array('date' => 'DESC'),
'limit' => 1,
),
'cascade_delete' => true,
),
);
which will act as a filter on the relation, which in this example will only show 1 record in the relation, which is the newest by date, of the records that have "value" in "column".
As you can only use literals in property definitions, you need to construct the where clause (which in your case should filter on language) in the models _init() method:
In regards to the compound PK's, this isn't an option with the latest stable Fuel 1.8.1 release as the ORM package resets any PKs that are manually given. Please refer to one of my previous posts here: https://fuelphp.com/forums/discussion/14973/primary-keys-always-get-reset-using-orm. We cannot move to the 1.9 dev branch right now as we would have to run through a massive amount of testing and my bosses my possibly murder me. :P
In your response above, this won't work for me as it will only return a single setting at a time. There are times where I need to fetch a setting for all languages in a single query. For example, we have multilingual forms where a user can edit their site title in all available languages.
I'm essentially trying to find a solution that doesn't hammer the DB and I can easily fetch the setting by the key name and language. For the time being, we will use the loop approach above as it's the only reliable way to do this at the moment without breaking anything.
We use two relations to the same table, one with, and one without the condition, so we can use either one.
BTW, it doesn't return a single setting, the suggestion is to dynamically filter on "language", so when you query a specific entity ID for a specific table, you get one record back, in the desired language, instead of a record for each language in the database that you have to iterate over to find the right one.
We use this all the time, for example for country names, currency tables, etc.
A condition on a relation is simlar to automatically changing:
In case of ORM, either a where clause is injected or an additional "join on" condition is added, depending on the type of query.
Challenge you have is that when you can't use compound PKs, no matter what you do at ORM level, you still need a method a select the required entity_id or the required key from the resultset, instead of being able to use