CodeIgniter Forums

Full Version: Help with ActiveRecord queries.
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

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.

El Forum

This is how I worked it out....

/*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->where_in('vendorId', $vendors);
        $query2 = $this->db->get();
        return $result2 = $query2->result();

El Forum

Looking at your original query I think it can be simplified

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:
$this->db->select('p.productId,, p.description, p.status');
$this->db->join('vendors v', "p.vendorId = v.vendorId and v.customerId = $customer_id");
$this->db->get('products p');