Using CI for controlling page hierarchical elements based on URI

[eluser]Phil Sturgeon[/eluser]
I did this and it REALLY hurt my head.

The model function is...

Code:

public function getByURL($segments = array())
    {
        // If the URI has been passed as a string, explode to create an array of segments
        if(is_string($segments))
        {
            $segments = explode('/', $segments);
        }
        
        // Work out how many segments there are
        $total_segments = count($segments);
        
        // Which is the target alias (the final page in the tree)
        $target_alias = 'p'.$total_segments;

        // Start Query, Select (*) from Target Alias, from Pages
        $this->db->select($target_alias.'.*');
        $this->db->from('pages p1');
        
        // Loop thorugh each Slug
        $level = 1;
        foreach( $segments as $segment )
        {
            // Current is the current page, child is the next page to join on.
            $current_alias = 'p'.$level;
            $child_alias = 'p'.($level - 1);
    
            // We dont want to join the first page again
            if($level != 1)
            {
                $this->db->join('pages '.$current_alias, $current_alias.'.parent_id = '.$child_alias.'.id');
            }
            
            // Add slug to where clause to keep us on the right tree
            $this->db->where($current_alias . '.slug', $segment);
    
            // Increment
            $level++;
        }
        
        // Can only be one result
        $this->db->limit(1);
        
        return $this->db->get()->row();
    }

It may look like a mess but if you can clean that down I will give you a medal. It works fine, just pass it $this->uri->segment_array();

The basic idea here is that I have pages that can be children of any other pages. Meaning I can have page1/page2/page3. The URI segment is called 'slug' and I check the slug at each level of the loop to keep the join accurate. This stops it wondering off and joining to incorrect children.

Let me know if this code is confusing as hell. I can write it up better this evening.
I alias each level p1, p2, p3, p4 etc dynamically and only request the highest level in the select(). That means if I have a page 4 levels deep, I don't want to return level 1, 2 or 3 data.