Data mapper pattern and automated updates of other objects

Question

Hi, I'm building a PHP application using the data mapper pattern to separate my DB from the domain objects. I have a mapper class that returns Site objects based on data from the DB and accepts existing Site objects to be saved back to the DB.

My problem is that in the system one (and only one) of all the sites has to be marked as the "primary" site, which means that if I set one as the primary, I'd like to be able to automatically unset the current primary.

So, something like:

$mapper = new Site_Mapper();
$site = $mapper->fetch(2);
$site->isPrimary = true;
$mapper->save($site);

Would somehow in the background automatically do this:

$mapper = new Site_Mapper();
$site = $mapper->fetch(1);
$site->isPrimary = false;
$mapper->save($site);

Question is, where should the logic for automatically updating the existing primary site go? It has to happen after the object is saved back to the DB, not before, in case the DB query fails and you're left with no site as the primary.

Cheers, Jack

Answer 1

Sounds like a job for a database trigger.

DELIMITER $$

CREATE TRIGGER test_trigger AFTER INSERT ON table
  FOR EACH ROW BEGIN
    IF NEW.isPrimary = 1 THEN 
       UPDATE table 
       SET isPrimary = 0
       WHERE id <> NEW.id;
    END IF;
  END$$

DELIMITER ;

Answer 2

Personally I would think that it makes the most sense to place the additional update logic with the Site_Mapper class, especially considering that you're dealing with the same table / mapper in both instances. You could simply override the save($siteObj) method so that it works like this:

 public function save($siteObj) 
 {
     // Save the passed object.

     $sql = "UPDATE site SET isPrimary = 1 WHERE id != ?";

     $stmt = new PDO_Statement($sql);

     $stmt->execute($siteObj->id);

 }

Obviously you could create either a custom save() function to do this perhaps a little more smoothly or you could use an if comparison to ensure that you actually need to run the update statement.