current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

Apparently active record cannot deal with temporary tables - huge oversight in my opinion. (ref: Codeigniter 2 active record — How do I create temporary table to apply second sort order? )

In light of this I am looking for a way to sort the result object produced by my query.

Here is my query:

    $this->db
                ->select('*')
                ->from('item_categories')
                ->where('item_categories.cat_id', $c)
                ->or_where('item_categories.parent_id', $c)
                ->or_where('item_categories.gparent_id', $c)
                ->join('item_categories_rel', 'item_categories_rel.cat_id = item_categories.cat_id', 'right')
                ->join('item_entries', 'item_entries.item_id = item_categories_rel.item_id','right')
                ->join('ratings_total', 'ratings_total.item_id = item_entries.item_id')
                ->order_by("item_name", "asc")
                ->limit($l, $s);
            $result = $this->db->get();

So for example my results contain a field called site_rating which is of type decimal(2,1) , how can I sort my result object by site_rating in asc/desc order? Any help greatly appreciated.

To be clear I do not want to change the current order, I want to apply a second sort order to the first result set - I have found I cannot do this with active record so would like to do it by sorting the result object if possible.

share|improve this question

1 Answer 1

up vote 0 down vote 
 $this->db
                ->select('*')
                ->from('item_categories')
                ->where('item_categories.cat_id', $c)
                ->or_where('item_categories.parent_id', $c)
                ->or_where('item_categories.gparent_id', $c)
                ->join('item_categories_rel', 'item_categories_rel.cat_id = item_categories.cat_id', 'right')
                ->join('item_entries', 'item_entries.item_id = item_categories_rel.item_id','right')
                ->join('ratings_total', 'ratings_total.item_id = item_entries.item_id')
                ->order_by("site_rating", "DESC")
                ->limit($l, $s);
            $result = $this->db->get();
share|improve this answer
    
sorry I may not have been clear in my question. I need them sorted as they are, I then need to apply a second sort. So for example my original query gets the first 50 results by item name, I then want to sort those results by their rating –  WebweaverD Sep 21 '12 at 13:52
1  
Can you make an example database on sql fiddle with a couple of items, then we can play with your database to give you the right result. –  Michael Dibbets Sep 21 '12 at 14:09
    
never heard of sql fiddle before.... I will do that and post back –  WebweaverD Sep 21 '12 at 14:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.