CODEWITHSUNDEEP
phpcodeigniteractiverecordsql-order-bycase
Атанас Атанасов
Атанас Атанасов

Reputation: 214

Codeigniter active record CASE working in select but can't order by its parameters

I have following select in my model:

$this->db->select("*, product.id as id, product.price as price, attribute.price as att_price, CASE WHEN(attribute.price > 0) THEN attribute.price ELSE product.price END as newprice");

When I fetch the results I can echo $product->newprice and it works well. The problem is that I want to order results by product price or attribute price if it exist. I can easy make it work by using similar CASE in order_by(). But instead I want to skip it and use newprice there.

When I try $this->db->order_by("newprice ASC") I get the following error:

Error Number: 105

Unknown column 'newprice' in 'where clause'

I can't understand why I can't use newprice in the order by function, but I have newprice when I fetch the results.

Upvotes: 0

Views: 748

Answers (1)

Pathik Vejani
Pathik Vejani

Reputation: 4491

Just use CASE WHEN statement in () as below, put whole query in temp:

$this->db->select("(*, product.id as id, product.price as price, attribute.price as att_price, (CASE WHEN(attribute.price > 0)                   
THEN attribute.price ELSE product.price END;) as newprice) as temp");
$this->db->order_by("temp.newprice ASC")

Upvotes: 1

Related Questions