public function children()
{
$params = array('vocab' => $this->vocabulary_id, 'left' => $this->mptt_left, 'right' => $this->mptt_right);
/**
* If we INNER JOIN the terms table with itself on ALL the descendants of our term,
* then descendants one level down are listed once, two levels down are listed twice,
* etc. If we return only those terms which appear once, we get immediate children.
* ORDER BY NULL to avoid the MySQL filesort.
*/
$query = <<<SQL
SELECT child.term as term,
\tchild.term_display as term_display,
\tchild.mptt_left as mptt_left,
\tchild.mptt_right as mptt_right,
\tchild.vocabulary_id as vocabulary_id,
\tchild.id as id
FROM {terms} as parent
INNER JOIN {terms} as child
\tON child.mptt_left BETWEEN parent.mptt_left AND parent.mptt_right
\tAND child.vocabulary_id = parent.vocabulary_id
WHERE parent.mptt_left > :left AND parent.mptt_right < :right
\tAND parent.vocabulary_id = :vocab
GROUP BY child.term
HAVING COUNT(child.term)=1
ORDER BY mptt_left
SQL;
return new Terms(DB::get_results($query, $params, 'Term'));
}