Delete sectors and their descendants - PHP

Question

I have the following structure table:

CREATE TABLE IF NOT EXISTS `setores` (
  `set_cod` int(10) NOT NULL AUTO_INCREMENT,
  `set_base` int(10) NOT NULL,
  `set_setor` varchar(50) NOT NULL,
  `set_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `set_status` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`set_cod`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

In this case, I can add 3 levels of sectors, as below:

Where this blue is the main sector, gray represents the subsector and white represents a third sector.

Well, my question is: How do I do that when excluding the Main sector, exclude all other sectors? What if I delete only the subsector? Is there any way to do this using left join?

For me to list as below, I did as follows:

# Busca Setores
public function busca_setores(){

    $this->db->where("set_base", '0');
    $this->db->order_by("set_setor", "ASC");
    $consulta = $this->db->get('setores')->result();

    foreach($consulta as &$valor){

        $this->db->where("set_base", $valor->set_cod);
        $valor->subsetor = $this->db->get('setores')->result(); 

        foreach($valor->subsetor as &$subsetor){
            $this->db->where("set_base", $subsetor->set_cod);
            $subsetor->subsubsetor = $this->db->get('setores')->result();
        }

    }

    return $consulta;   
}

Can we adapt this reality, to mount exclusion by sectors?

Answer 1

One idea is to use the ON DELETE CASCADE on the foreign key (from the table to itself):

CREATE TABLE IF NOT EXISTS `setores` (
  `set_cod` int(10) NOT NULL AUTO_INCREMENT,
  `set_base` int(10) NOT NULL,
  `set_setor` varchar(50) NOT NULL,
  `set_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `set_status` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`set_cod`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

ALTER TABLE `setores`
    ADD CONSTRAINT `relaciona_pai` FOREIGN KEY (`set_base`)
    REFERENCES `setores` (`set_cod`)
    ON DELETE CASCADE ON UPDATE CASCADE;

The SQL for the exclusion would be this:

/* Vamos supor que 5 é o código do setor pai que você quer excluir. */
DELETE FROM setores WHERE set_base = 5;

And note that in the code above I used ENGINE=InnoDB instead of ENGINE=MyISAM. This is important because Myisam does not support the concept of foreign keys. Although Mysql keeps them in the table settings, the corresponding integrity restrictions are not respected by Myisam, which completely ignores them.

Answer 2

Another possibility is to solve this with a trigger. This approach is harder to maintain, but dispenses with the need to have the self-relationship table (but you can choose to have it anyway).

DELIMITER $
CREATE TRIGGER `apagar_subsetores` BEFORE DELETE ON `setores` FOR EACH ROW
BEGIN
    DELETE FROM setores WHERE set_base = old.set_cod;
END$
DELIMITER ;