Un-promote all other nodes when one node is promoted to front page

Question

I have an instance for a specific content type that at any given time there can only be ONE item promoted to the front page. I have a view that looks for this value and displays the item in a block on the front page if it is promoted. The problem is that it is setup to only show ONE item, so the editor has to remember to go and UN-promote any other node after they promote the one they want. I'm trying to figure out a way that when the user clicks the promote to front page check box on the node edit form and saves the node, all other nodes are auto un-promoted. I tried to hook into hook_node_presave, but I can't seem to get the query right to un-promote all the other nodes, because I am using revisions, so I'm not entirely sure what to update.

This is what I have right now that is not working, I think because I am using revisions:

function MY_MODULE_node_presave($node){
  if ($node->type == 'career' && $node->promote == 1) {        
    $sql = "
    UPDATE {node} n
    SET n.promote = 0
    WHERE n.type = 'career'
    ";       
    db_query($sql);        
  }
}

Answer 1

In the views, you can simply sort the view by updated date on filtered promoted nodes. I think this will solve the issue without any custom code. This might not work in all cases. It will fail when content writer promotes a new node, then updates old promoted node.

You can use below code. For your specific case, you can use $base_root to apply the change on a particular site only.

function MY_MODULE_node_presave($node){
  // if $base_root is equal to my.specific.domain.site, then perform the db_update.
  if ($node->type == 'career' && $node->promote == 1) {   
    $update = db_update('node')
      ->fields(array('promote' => 0))
      ->condition('node.promote', 1, '=')
      ->condition('node.type', 'career', '=')
      ->condition('node.nid', $node->nid, '<>');
    $update.execute();
    // Do this if you still face the problem. Also please comment if node_revision update was necessary.
    // $update = db_update('node_revision')
    //   ->fields(array('promote' => 0))
    //   ->condition('node.promote', 1, '=')
    //   ->condition('node.type', 'career', '=')
    //   ->condition('node.nid', $node->nid, '<>');
    // $update.execute();     
  }
}