Welcome Guest, Not a member yet? Register   Sign In
Need help with many to many table join.
#3

[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();
    }


Messages In This Thread
Need help with many to many table join. - by El Forum - 04-30-2010, 04:15 PM
Need help with many to many table join. - by El Forum - 04-30-2010, 06:43 PM
Need help with many to many table join. - by El Forum - 05-01-2010, 06:28 PM



Theme © iAndrew 2016 - Forum software by © MyBB