Joining tables with Active Record works fine, but how do I add a WHERE clause into the mix?

[eluser]markup2go[/eluser]
Hi I'm trying to join tables in my model to gather directly related data of a result.

I can join tables just fine like this:

Code:

// Selects all jobs, adds category join in jobtype columns
    function view_category(){
        $this->db->select("jobs.*, categories.name as category_name, categories.slug as category_slug, categories.description as category_description, jobtypes.name as jobtype_name, jobtypes.slug as jobtype_slug, jobtypes.description as jobtype_description", FALSE);
        $this->db->where('category_id', $this->uri->segment(4));
        $this->db->join('categories', 'jobs.category_id = categories.id', 'left');
        $this->db->join('jobtypes', 'jobs.jobtype_id = jobtypes.id', 'left');
        $query = $this->db->get('jobs');
        if($query->num_rows() > 0) {
            return $query->result();
        }
    }

But now I need to add a where clause into it somehow, on the jobs table only but still joining the other 2.

Something like:

Code:

function get_job(){
        // Get one job depending on url

        // original query without the joins
        //$query = $this->db->query("SELECT * FROM `jobs` WHERE `id` = '".$this->uri->segment(4)."' LIMIT 1");
        
      
        $this->db->select("jobs.*");
        $this->db->where('id', $this->uri->segment(4));
        
        $this->db->select("categories.name as category_name, categories.slug as category_slug, categories.description as category_description, jobtypes.name as jobtype_name, jobtypes.slug as jobtype_slug, jobtypes.description as jobtype_description", FALSE);
        $this->db->join('categories', 'jobs.category_id = categories.id', 'left');
        $this->db->join('jobtypes', 'jobs.jobtype_id = jobtypes.id', 'left');
        $query = $this->db->result("jobs");
        if($query->num_rows() > 0) {
            return $query->result();
        }
    }

This obviously isn't working and I'm pretty frustrated so I figured I would ask and see if anyone can correct me in the right direction.

Thanks!

[eluser]TheFuzzy0ne[/eluser]
id looks ambiguous to me. I'd imagine you'd need to use something like:

Code:

$this->db->where('jobtypes.id', $this->uri->segment(4));

or

Code:

$this->db->where('categories.id', $this->uri->segment(4));

[eluser]markup2go[/eluser]
Thanks, you were exactly right.