Convert SQL containing a subquery and multiple OR WHERE conditions to CodeIgniter's active record syntax

Question

I am struggling to create an active record statement to do what I want, here is my current attempt:

$this->db
    ->select('*')
    ->from('item_entries')
    ->where('is_pick', 'y')
    ->join('item_categories_rel', 'item_categories_rel.item_id = item_entries.item_id')
    ->join('item_categories', 'item_categories.cat_id = item_categories_rel.cat_id')
    ->join('ratings_total', 'ratings_total.item_id = item_entries.item_id')
    ->where('item_categories.cat_id', $cat_id)
    ->or_where('parent_id', $cat_id)
    ->or_where('gparent_id', $cat_id)
    ->order_by("item_name", "desc");  
$query2 = $this->db->get();
if (is_object($query)) {
    return $query2->result_array();
} else { 
    return $query2;
}

This translates to the following sql:

SELECT *
FROM (`item_entries`)
JOIN `item_categories_rel` ON `item_categories_rel`.`item_id` = `item_entries`.`item_id`
JOIN `item_categories` ON `item_categories`.`cat_id` = `item_categories_rel`.`cat_id`
JOIN `ratings_total` ON `ratings_total`.`item_id` = `item_entries`.`item_id`
WHERE `is_pick` =  'y'
AND `item_categories`.`cat_id` =  '3'
OR `parent_id` =  '3'
OR `gparent_id` =  '3'

Now, what I want (although I am aware I cant do temp tables in active record) is the equivalent of this:

SELECT *
FROM (
    SELECT *
    FROM (`item_entries`)
    JOIN `item_categories_rel` ON `item_categories_rel`.`item_id` = `item_entries`.`item_id`
    JOIN `item_categories` ON `item_categories`.`cat_id` = `item_categories_rel`.`cat_id`
    JOIN `ratings_total` ON `ratings_total`.`item_id` = `item_entries`.`item_id`
    WHERE `is_pick` =  'y'
    AS T
)
WHERE `item_categories`.`cat_id` =  '3'
OR `parent_id` =  '3'
OR `gparent_id` =  '3'

How can retrieve this result in active record?

Please note - I have not checked the above SQL so it may be slightly wrong but hopefully you get what I am trying to do.

Answer 1

You can't do that with CodeIgniter Active Record. It's not mean to do that. You can use this library instead as a workaround.