Dynamic table fields for CakePHP

Question

I'm trying to build an app with dynamic table fields. The idea is that the admin can define which fields for each item is available without altering the MySQL tables.

The data entered in the fields should be various types, e.g. text, currency and boolean, so it would be possible to add a suffix like $ or display an image for booleans. Also the Fields should be assigned to Groups (to separate them in HTML-fieldsets). Finally an item has one value per field.

I want the add and edit forms for items to show every possible field.

So am I right that I need the following Models:

• Item (hasMany Value)
• Field (belongsTo FieldGroup & FieldType (also hasMany Value??))
• FieldGroup (hasMany Field)
• FieldType (hasMany Field)
• Value (belongsTo Item & Field)

to get what i need?

The part I really don't know how to do: it is only allowed to have one value per item, but because the model Item hasMany Values there can be more than one.

And how would I get the Fields with Groups and Types to the form in Item? I'm trying to get them with loadModel('Group'); (correct?) and then assign the array $fieldsByGroups = $this->Group->find('all') to the add/edit templates and do a foreach($fieldsByGroups) to create the form inputs. Is that an appropiate method?

The Values table has 4 Fields: id, item_id, field_id, value (LongText as i saw in the wordpress table structure. I don't need different tables for each FieldType as suggested in some eav-articles (of which none is what i need)). Basically i don't need id, but I think CakePHP needs a primaryKey and can't handle an index of item_id and field_id.

thanks,

manuel

Answer 1

A solution that I built recently into a behaviour would use the model CustomField to describe the extra fields either globally on an entire model or specific records within that model. I would also have a text field called custom_fields on each model that used the behaviour that would store a json array with the custom field data.

After that I just added some callback magic to make it output the custom fields in the right format when I queried for it and save the custom fields as if they were normal fields.

That was a quick and easy solution that I made but if your doing this as a core part of your application you should consider looking at No-SQL databases. There is a good datasource for CakePHP that works with MongoDB at http://github.com/ichikaway/cakephp-mongodb