So I’ve got a problem someone might find interesting. I have a table of user settings for my application. The table is designed somewhat like this:
COLUMNS:
id accountId setting value
Example Data:
12 132 backgroundColor 0xFFFFFF
13 132 foregroundColor 0x000000
My question is, how can I access all of the records for a given accountId (I am already able to get these from the model). But I need to update each individual row. The reason I set the database up this way was so that I could easily add new settings without adjusting the database, model, views, etc…everytime a new setting needs to get added.
Does anyone have any ideas? I’m wondering if there is maybe a way to pivot the result set so the ‘setting’ values become attributes in the model, and the ‘value’ values become attribute values?
As I understand it, this could take the unique "setting" column and make each one the array key, with corresponding array value set from the "value" column?
This doesn’t necessarily address the problem of being compliant with Yii’s Model coding standard? If I were to access the data in the View file, would I simply do something like this:
Has anyone done anything close to this before? I guess I’m trying to adapt/pivot the result set, dynamically, which sort of seems wrong in a way and against Yii’s methods of CRUD’ding data.
Hmmm…I’ll keep reading to see if I can find any other examples.
This really looks like what I’m trying to achieve, I don’t think I’ll run into too many problems abandoning what I’ve done so far…I’m pretty new to Yii, so I guess I’ve got to change my mindset a little when thinking about data and how to work with it.
This is probably too late for you but I use another model to pivot my data.
Consider the application of a user questionnaire where I want to be able to adapt the questionnaire for different situations. I therefore store the results in a similar way userid, questionid, answerid and user an intermediate model (questionnaire) to transform both the load and save. It can often be done very simply.
So the load would loop like this:-
$model=new Mhistory;
$responses=Response::model()->findAll('user_id=:userID',array(':userID'=>$uid));
if($responses===null)
throw new CHttpException(404,'The requested page does not exist.');
foreach($responses as $response) {
$v=$response->question_ref;
//check to see if the response was packed as a pipe delimited array
$s=strpos($response->response_text, "|");
if ($s !== false) {
$model->$v=explode("|",$response->response_text);
} else {
$model->$v=$response->response_text;
}
}
return $model;
SAVE method
$responses=$_POST['Mhistory'];
$model=Response::model();
$transaction=$model->dbConnection->beginTransaction();
try
{
// within a transaction, so I delete original records and then insert new
$model->deleteAll( "user_id = :uid ", array(':uid' => $uid));
foreach($responses as $i=>$response)
{
$v=new Response;
$v->user_id=$uid;
$v->question_ref=$i;
if (is_array($response)) {
$v->response_text=implode("|",$response);
} else {
$v->response_text=$response;
}
if($v->save()) {
$this->redirect(array('view','id'=>$mhistory->id));
}
}
$transaction->commit();
//echo "finished loop";
}
Thanks for posting your solution. I’ve stuck with the settings extension for now, but I’ll also look into your solution. Just skimming over it, it seems to have some merits on its own that the settings plugin doesn’t…mainly that is still complies with the traditional Model class structure. Also the settings plugin is designed for only a single table, whereas I might need multiples.
