Help with JOIN active record class

Help with JOIN active record class - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Help with JOIN active record class (/showthread.php?tid=31362)

Help with JOIN active record class - El Forum - 06-16-2010

[eluser]chefnelone[/eluser]
Hello

I'm using active records with JOIN in this way:

Code:
$this->db->join('products_images', 'products_images.id_product = products.product_id');

$products = $this->db->get('products');

$data['products'] = $products->result();

it works fine but the problem is:

let say I have 3 records in table 'products' with id_product: 1, 2 and 3
and 2 records in table 'products_images' with id_product: 1 and 2

When I get $data['products'] the record with id_products = 3 is missing. I can see that if the JOIN is not made the record is no added to the results.
Is there any way to get the record even if that doesn't exist in the 'products_images' table??

Help with JOIN active record class - El Forum - 06-16-2010

[eluser]WanWizard[/eluser]
Read up on the different types of JOINS. Wikipedia has a good article on them.

In this case, you need a left join:

Code:
$this->db->join('products_images', 'products_images.id_product = products.product_id', 'LEFT');

Note that all fields missing in the result (because there was no matching record to join) will have the value NULL. You'll have to test for this in your application.

Help with JOIN active record class - El Forum - 06-16-2010

[eluser]chefnelone[/eluser]
[quote author="WanWizard" date="1276706193"]Read up on the different types of JOINS. Wikipedia has a good article on them.

In this case, you need a left join:

Code:
$this->db->join('products_images', 'products_images.id_product = products.product_id', 'LEFT');

Note that all fields missing in the result (because there was no matching record to join) will have the value NULL. You'll have to test for this in your application.[/quote]
thanks wanwizard. will take a look at the wiki

Help with JOIN active record class - El Forum - 09-02-2010

[eluser]bluepicaso[/eluser]
It helped great, but i a one to may relationship. Its like for each tour package i have many images.
with the above code, the package gets displayed = number of images of that project.
I have pagination with it too
my model code.

Code:
$this->db->select('*');

        $this->db->from('package');

        $this->db->where('zone', $zone);

        $this->db->join('images', 'images.pak_id = package.pac_id', 'LEFT');  

        $this->db->limit($num, $offset);

        $query = $this->db->get()->result_array();

        foreach($query as $row)

        {

            $data[] = $row;

        }

        return $data;

        $this->db->close();

How do i get this done?
Please help