Need help with many to many table join.

[eluser]Ichabodcole[/eluser]
Hello there, I can't seem to wrap my head around a good and efficient way to join all my tables.

I'm creating a online portfolio and building the backend using codeigniter. The main purpose of the backend is to store and retrieve project information.

In addition to the main 'projects' table I have a 'project_cats' (categories), 'project_clients', 'project_types', 'project_credit_list', and 'project_credits'.

In my database I have a "projects" table, each project needs to have a list of credits like designer, programmer, animator, etc. So i have created a separate table called "project_credits" that contains individual credit types, then I have a third linker table called 'project_credit_list' that stores a "project_id" and a "credit_id" for each row.

So what I don't know how to do is properly get the results of the credits list and join it with my results, and I also don't know how to then properly loop through the credits for each project.

Below is the code I'm using to join all the tables together. It basically returns all projects that match a specific category name.
It all works fine until I get to the credit list and then I'm just failing my arms in the air.

Any help is very appreciated! Thanks.

Code:

function get_projects_by_category($type, $limit=10)
    {    
        /* I first select from the project_cats table getting the category that matches my $type var.*/
        $this->db->select('*, project_cats.name AS cat_name');
        $this->db->from('project_cats');
        $this->db->where('project_cats.name', $type);

        /* Now I join the projects table where the projects table cat_id value matches the project_cats table id value.*/
        $this->db->join('projects', 'projects.cat_id = project_cats.id', 'left');

        /* Now I join the project_types table where the project_types table id value matches the projects table type_id value.*/
        $this->db->join('project_types', 'project_types.id = projects.type_id', 'left');

        /* Now I join the project_clients table where the project_clients table id value matches the projects table client_id value.*/
        $this->db->join('project_clients', 'project_clients.id = projects.client_id', 'left');
                
        /* Now I have no idea how to join the project_credit_list with the project_credits table and then to the projects*/
        //$this->db->join('project_credit_list', 'project_credit_list.project_id = projects.id', 'left');

        $this->db->order_by('projects.position');
        $this->db->limit($limit);
        
        $query = $this->db->get();
        
        return $query->result();
    }

[eluser]seanloving[/eluser]
[quote author="Ichabodcole" date="1272683750"]

Code:

/* Now I have no idea how to join the project_credit_list with the project_credits table and then to the projects*/
        //$this->db->join('project_credit_list', 'project_credit_list.project_id = projects.id', 'left');

[/quote]
maybe try something like this

Code:

->join('project_credit_list', 'project_credit_list.project_id = projects.id AND project_credit_list.project_credit_id = project_credits.id', 'left')

[eluser]Ichabodcole[/eluser]
Well, I figured out a way around it, basically breaking it into two functions in my model.
The first retrieves the projects, then from those results I run a loop in php and get the credits for each project and add them to and array in the projects var I'm sending to the view.

It looks like I could do this with one query using mysqls GROUP_CONCANT function, then exploding the string, but this is unsupported in active record so I think I won't for now.

Below is the code that is in my controller:

Code:

function index()
    {
        $data = array();
        $this->load->model('Projects_model');
        
        $pm = $this->Projects_model;
        
        $category = 'websites';
        $data['projects'] = $pm->get_projects_by_category($category, 5);
        
        foreach($data['projects'] as $key=>$project){
            
            $project_id = $project->the_project_id;
            $project->credit_list[$key] = $pm->get_project_credits($project_id);            
        }
        
        $this->load->view('home_view', $data);
    }

And this is in my model:

Code:

function get_projects_by_category($type, $limit=10)
    {    
        
        $this->db->select('*, project_cats.name AS cat_name, projects.id AS the_project_id')->from('project_cats')->where('project_cats.name', $type);
        $this->db->join('projects', 'projects.cat_id = project_cats.id', 'left');
        $this->db->join('project_types', 'project_types.id = projects.type_id', 'left');
        $this->db->join('project_clients', 'project_clients.id = projects.client_id', 'left');
        $this->db->order_by('projects.position')->limit($limit);
        
        $query = $this->db->get();
        
        return $query->result();
    }
    
    function get_project_credits($project_id)
    {
        $this->db->select('*');
        $this->db->from('project_credit_list');
        $this->db->where('project_credit_list.project_id', $project_id);
        $this->db->join('project_credits', 'project_credits.id = project_credit_list.credit_id', 'left');
        
        $query = $this->db->get();
        
        return $query->result();
    }