Help with ActiveRecord queries.

[eluser]Unknown[/eluser]
Hi guys...

I'm kinda new to the CI world....

I'm currently working on a project where all the queries are created using the ActiveRecord Class.

I find AR very easy to use.... nevertheless I've been needing to excecute this particular query wich I have no clue on how to build using ActiveRecord.

the query is:
SELECT productId, name, description, status
FROM products
WHERE vendorId in (SELECT vendorId
FROM vendors
WHERE customerId = $customer_id)

Thanks for your help.

[eluser]Unknown[/eluser]
This is how I worked it out....

Code:

/*SELECT vendorId
         FROM vendors
         WHERE customerId = $customer_id
         AND status = 'C'
         */    
        $query = $this->db->get_where('vendors', array('status' => 'C', 'customerId' => $customer_id));
        $result = $query->result();
        
        //Taking ids
        foreach ($result as $vendor)
        {
            $vendors = $vendor->vendorId;
        }

        /*SELECT productId, NAME, description, STATUS
         FROM products
         WHERE vendorId IN (SELECT vendorId
         FROM vendors
         WHERE customerId = $customer_id)*/
        $this->db->select('productId, name, description, status');
        $this->db->from('products');
        $this->db->where_in('vendorId', $vendors);
        $query2 = $this->db->get();
        return $result2 = $query2->result();

[eluser]rogierb[/eluser]
Looking at your original query I think it can be simplified

Code:

SELECT p.productId, p.NAME, p.description, p.STATUS
FROM products p
join vendors v on ( p.vendorId = v.vendorId and v.customerId = $customer_id)

which would make your AR:

Code:

$this->db->select('p.productId, p.name, p.description, p.status');
$this->db->join('vendors v', "p.vendorId = v.vendorId and v.customerId = $customer_id");
$this->db->get('products p');