Help with JOIN active record class

#1
[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??

#2
[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.

#3
[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

#4
[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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.