multiple join in codeigniter not working as expected

Question

there is 4 table

option_group(id, optionGroupName)

category_optiongroup(categoryId, optionGroupId, inOrder)

product_option(productId, optionGroupId, optionId)

option(id, optionValue)

some products has not inserted its optionValue yet in the product_option table but I want to get theme all Whether is inserted or not. for example size of specific product has not set yet.

here is my model but it only return all optionValue that is set.

is it some how possible to do it with IFNULL()? if not doesn't matter

IFNULL(optionValue, 'Not Set')

$this->db->select('option.optionValue')
   ->from('category_optiongroup')
   ->where('categoryId', $data['categoryId'])
   ->join('product_option', 'product_option.productId='.$productId.
      ' AND product_option.optionGroupId=category_optiongroup.optionGroupId', 'left')
   ->join('option', 'option.id=product_option.optionId')
   ->order_by('category_optiongroup.inOrder', 'ASC');
$query = $this->db->get();
return $query;

Answer 1

two tips:

1st: both option and product_option tables needs left join

2nd: certainly IFNULL() needs an alias to call

$this->db->select('IFNULL(`option`.`optionValue`, "Not Set") AS optionValue', FALSE)
   ->from('category_optiongroup')
   ->join('product_option', 'product_option.productId='.$productId
            .' AND product_option.optionGroupId=category_optiongroup.optionGroupId', 'left')
   ->join('option', 'option.id=product_option.optionId', 'left')
   ->where('categoryId', $data['categoryId'])
   ->order_by('category_optiongroup.inOrder', 'ASC');
$query = $this->db->get();
return $query;