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;

score 1 · Accepted Answer · answered Sep 07 '21 at 03:55

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;

Please add further details to expand on your answer, such as working code or documentation citations. — Community, Sep 07 '21 at 04:59

nbk · Answer 2 · 2021-09-06T23:28:53.830

0

You can use IFNULL like this

$this->db->select('IFNULL(`option`.optionValue,"Not Set")',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')
   ->where('categoryId', $data['categoryId'])
   ->order_by('category_optiongroup.inOrder', 'ASC');
$query = $this->db->get();
return $query;

edited Sep 06 '21 at 23:28

answered Sep 06 '21 at 21:57

nbk

45,398
8
30
47

missed one parentheses try the changed code – nbk Sep 06 '21 at 22:07
now when i want to return the result on controller i get Undefined property: stdClass::$optionValue – Hossein Sep 06 '21 at 22:08
even this one not working```$this->db->select('option.optionValue') ->select('IFNULL(option.optionValue, "Not Set")', FALSE)```.(the error of undefinded property is gone but not showing the whole optionGroup like before) – Hossein Sep 06 '21 at 22:19
it doesn't matter using IFNULL or not, just someone give proper answer please – Hossein Sep 06 '21 at 22:38
you addeedd the jojn s behind the where cvlause that ios wrong, it must be before theorder and behind the joins – nbk Sep 06 '21 at 22:48
$this->db->select('option.optionValue') ->select('IFNULL(option.optionValue, "Not Set")', 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') ->where('categoryId', $data['categoryId']) ->order_by('category_optiongroup.inOrder', 'ASC'); $query = $this->db->get(); return $query; – Hossein Sep 06 '21 at 22:55
take look at this how this one works just fine [link](https://stackoverflow.com/questions/5940486/ifnullcountid-0-in-codeigniter) for him i mean – Hossein Sep 06 '21 at 23:01
this is the same3 as i wrote you query has some bug that i can't odetify so run https://stackoverflow.com/questions/6142099/how-to-print-sql-statement-in-codeigniter-model and see the queray as it is and see if it runs in workbench or – nbk Sep 06 '21 at 23:10
take look->__(SELECT `option`.`optionValue`, IFNULL(option.optionValue, "Not Set") FROM `category_optiongroup` LEFT JOIN `product_option` ON `product_option`.`productId`=174 AND `product_option`.`optionGroupId`=`category_optiongroup`.`optionGroupId` JOIN `option` ON `option`.`id`=`product_option`.`optionId` WHERE `categoryId` = '35' ORDER BY `category_optiongroup`.`inOrder` ASC) – Hossein Sep 06 '21 at 23:18
ran it in console still does not show the missing product-option that is not set yet – Hossein Sep 06 '21 at 23:26
i get a error as `option`m is a reserved word, so i dont' get where you get, try the code it should now run – nbk Sep 06 '21 at 23:29
with miner change adding backtick it is still return all of inserted options not the set part! – Hossein Sep 06 '21 at 23:41
it always return inserted optionValue but not missed one as null (I want to show theme all in html table ) – Hossein Sep 06 '21 at 23:44

multiple join in codeigniter not working as expected

2 Answers2