SQL statement - how do I do this in active record?

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.

You'll want to have a look at [this](https://github.com/NTICompass/CodeIgniter-Subqueries). — Kemal Fadillah, Sep 29 '12 at 15:59
I guess that will be plan b but surely there is a way to do this in AR — SwiftD, Sep 29 '12 at 16:01
Then go with plan B. CodeIgniter Active Record is not meant for subqueries. So, no, there's no way to do this in AR. At least not without some modification to the core files. — Kemal Fadillah, Sep 29 '12 at 16:06
Thanks for your help - Could you submit the link as an answer as it seems to be the solution with some minor modification - http://stackoverflow.com/questions/10768582/php-codeigniter-error-while-executing-library-subquery-php (although I dont yet have my query syntax right it is doing what I want) — SwiftD, Sep 29 '12 at 16:23

score 1 · Accepted Answer · answered Sep 29 '12 at 16:30

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.

answered Sep 29 '12 at 16:30

Kemal Fadillah

9,760
3
45
63

SQL statement - how do I do this in active record?

1 Answers1