How do you implement a model based off of a database table that has no single column primary key? The table has a composite primary key but no unique column. I want to create a model with this table as its primary table, but as far as I can tell the model requires a unique id field.
The table holds custom field values for services and consists of the following 3 columns: field_id, service_id, value. I need to be able to reference all of the custom field values that exist for a service and also to reference all of the services linked to a particular value.
The two main problems I'm having are..
1) Constructing a model based on the custom fields values table which has no unique column
2) Accomplishing a join with two 'ON' conditions. For example:
services JOIN fields ON
services.id = field.service_id
JOIN values ON
field.id = values.field_id AND
values.service_id = services.id
1) See my and Romans answers in following question some days ago: Junction Table with mutiple primary keys
2) That's simple - you should add next join not to model itself, but to previous "join"
$j_fields = $services_model->join('fields');
$j_values = $j_fields->join('values'); // $j_fields here not $services_model
You an also define second condition (values.service_id=services.id) somehow, but to tell the truth I don't have working example ready to post. You have to use $model->dsql->andExpr() as far as I remember.
I guess you can make it using addCondition (moving that condition to WHERE) or by using DSQL, too.