PyroCMS v0.9.7.4 - an open-source modular general purpose CMS

[eluser]Phil Sturgeon[/eluser]
OK scratch that, I was close but not entirely.

The magic query we need to use is this:

SELECT p3.*
FROM pages AS p1
LEFT JOIN pages AS p2 ON p2.parent = p1.id
LEFT JOIN pages AS p3 ON p3.parent = p2.id
WHERE p1.slug = 'parent' AND p2.slug = 'child' AND p3.slug = 'grandchild';

This will support everything we need from it. It will pick the correct tree and make sure even if there are two "child" or "grandchild" slugs anywhere else it will still take the right path.

I have roughed up some code (not tested).

Code:

// Work out how many segments there are
$total_segments = count($url_segments);

// Which is the target alias (the final page in the tree)
$target_alias = 'p'.$total_segments;

// Select everything (*) for the target alias
$this->db->select($target_alias.'.*')->from('pages p1');

$i = 1;
foreach( $url_segments as $slug )
{
    // Current is the current page, child is the next page to join on.
    $current_alias = 'p'.$i);
    $child_alias = 'p'.$i + 1;
    
    // We dont want to join the first page again
    if($i > 1)
    {
        $this->db->join('pages '.$current_alias, $child_alias.'.parent = '.$current_alias.'.id');
    }
    
    // Add slug to where clause to keep us on the right tree
    $this->db->where($current_alias . '.slug', $slug);
    
    $i++;
}

$query = $this->db->get();

Something like that should do. Sudden inspiration! :lol: