I have a collection of tables like so:
logbook
, responsibility
, user
Responsibilities are reused - but only one distinct responsibility per logbook. Each responsibility has one user. A user can have multiple responsibilities (on multiple logbooks). I have a table to join these together:
logbook_responsibility
, with three colummns (each making up the primary key) - logbook_id
, responsibility_id
and user_id
, which are all foreign keys. In my schema.xml
this table is set isCrossRef="true"
.
When updating a responsibility to a new user, I am passed in an array in $_POST
in the form of $_POST['responsibility'][2] = 5
, $_POST['responsibility'][7] = 2
, etc. I have a function that is supposed to update them:
public function updateResponsibilities($options = null)
{
foreach ($options['responsibility'] as $k => $v) {
$user = UserQuery::create()
->filterByLogbook($this->logbook)
->findOneById($v);
$logbookResponsibility = LogbookResponsibilityQuery::create()
->filterByLogbook($this->logbook)
->filterByResponsibilityId($k)
->findOne();
if (is_null($user) || is_null($logbookResponsibility)) {
break;
}
$logbookResponsibility->setUser($user)->save();
}
return true;
}
Here I loop through the options, create a new user with the supplied ID, filter by the current logbook to make sure they're allowed to be responsible for this logbook, and grab the first one. This works fine (dumped with ->toArray()
:
array(11) {
["Id"]=>
int(2)
["CompanyId"]=>
int(1)
["CurrentLogbookId"]=>
int(1)
["OtherFieldsHere..."]=>
// etc, etc
}
Next I grab a LogbookResponsibility, filtering again by the current logbook and the responsibility ID. At this stage the new user ID is shown correctly:
array(3) {
["LogbookId"]=>
int(1)
["ResponsibilityId"]=>
int(1)
["UserId"]=>
int(1)
}
I then update this object with the previously grabbed user, and save. I get no errors doing this, and inspecting the object afterwards shows that the object, at least, has updated:
array(3) {
["LogbookId"]=>
int(1)
["ResponsibilityId"]=>
int(1)
["UserId"]=>
int(2)
}
But, without touching anything else, looking at the database after, the user ID is still 1. I've tested running a query like:
UPDATE logbook_responsibility SET user_id = 1 WHERE logbook_id = 1 AND responsibility_id = 1;
...which works without error.
My actual question is two-fold:
1) Why isn't it saving? (Is it to do with my cross ref table? Is there a problem with cross ref tables with more than 2 columns?)
2) Is there generally a better way to achieve my setup? *(I tried achieving the same with a responsibility object, but didn't see a way to update the logbook_responsibility from there)*
Happy to take suggestions as well as an actual answer!
Propel::getConnection()->getLastExecutedQuery();
, that might point you in the right direction – billyonecan