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

[eluser]Turv[/eluser]
[quote author="Phil Sturgeon" date="1251226630"]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:[/quote]

Fantastic! Got it working!

This is now my getBySlug function

Code:

public function getBySlug($slug = '', $lang = NULL)
    {
        /**
         * Slug is now an Array
         * Perform Query to determine correct Page ID
         */
         if(is_array($slug)) {
            // Count Total Items in Array
            $TotalSlugs = count($slug);
            // Set Target Alias
            $TargetAlias = 'p'.$TotalSlugs;

            // Start Query, Select (*) from Target Alias, from Pages
            $this->db->select($TargetAlias.'.*');
            $this->db->from('pages AS p1');
            
            // Set the Limit, Can only be one result!
            $this->db->limit(1);
            
            // Initialise Counter
            $i = 1;
            
            // Loop thorugh each Slug
            foreach( $slug as $page ) {
            
                // Current is the current page, child is the next page to join on.
                $CurrentAlias = 'p'.$i;
                $ChildAlias = 'p'.($i - 1);
        
                // We dont want to join the first page again
                if($i > 1)
                    $this->db->join('pages AS '.$CurrentAlias, $CurrentAlias.'.parent = '.$ChildAlias.'.id', 'left');
    
                // Add slug to where clause to keep us on the right tree
                $this->db->where($CurrentAlias . '.slug', $page);
        
                // Increment
                $i++;
            }
         }
         // If $slug is not an array, must be single page, just use it directly
         else
         {
            $this->db->where('slug', $slug);
         }
        
        if($lang == 'all')
        {
            exit('where did this code go?! tell me if you see this message [email protected]!');
        }  
            
        elseif($lang != NULL)
        {
            $this->db->where( (is_array($slug) ? $TargetAlias.'.lang' : 'lang'), $lang);
        }
        
        return $this->get($lang);
    }

There is one problem with this code it seems, and it's the From clause. This is the output query of the above (using my test)

Code:

SELECT p3.*
FROM (pages AS p1, pages)
LEFT JOIN pages AS p2 ON p2.parent = p1.id
LEFT JOIN pages AS p3 ON p3.parent = p2.id
WHERE `p1`.`slug` = 'test-page'
AND `p2`.`slug` = 'sub-page'
AND `p3`.`slug` = 'sub-sub-page'
AND `p3`.`lang` = 'en'
LIMIT 1

This produced 10 results apparently, Hence the limit clause. However changing the from clause to this makes it work perfectly.

Note: The only difference is the from clause is 'FROM pages as p1' where as CI produces FROM (pages AS p1, pages). Why is it doing that, and can we make it produce what we want?

Code:

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` = 'test-page'
AND `p2`.`slug` = 'sub-page'
AND `p3`.`slug` = 'sub-sub-page'
AND `p3`.`lang` = 'en'
LIMIT 1

Great job bud, I would have never thought about doing that.