I have a issue. My sql query don't work in codeigniter, when i try to order by specific value Here's example:
$this->db->select('*'); $this->db->from('Questions'); $this->db->where('Questions.Status !=', 0); $this->db->order_by('IdQuestion', 'DESC'); $this->db->order_by('(CASE WHEN Status = 3 THEN 1 ELSE 2 END)', 'DESC'); //Here's wrong... But i don't receive valid result. Someone can help. Second order_by statement is wrong.
CASE don't work correct.
You can try this solution for your problem :
<?php $sub_query_from = '(SELECT Questions.*, (CASE WHEN Questions.Status = 3 THEN 1 ELSE 2 END) as questions_status from Questions WHERE Questions.Status != 0 ORDER BY IdQuestion DESC) as sub_questions'; $this->db->select('sub_questions.*'); $this->db->from($sub_query_from); $this->db->order_by('sub_questions.questions_status', 'DESC'); $query = $this->db->get(); $result = $query->result(); echo "<per>"; print_r($result); exit; ?> Hope it will helps.
My solutions is that: To create view which will contains all results ordered, before select this view in codeigniter model
Example:
select (case when (`parajurist`.`intrebari`.`Status` = 2) then 1 else 2 end) AS `Second`,`parajurist`.`intrebari`.`IdIntrebare` AS `IdIntrebare`,`parajurist`.`intrebari`.`Titlu` AS `Titlu`,`parajurist`.`intrebari`.`Descriere` AS `Descriere`,`parajurist`.`intrebari`.`NumePrenumeP` AS `NumePrenumeP`,`parajurist`.`intrebari`.`EmailP` AS `EmailP`,`parajurist`.`intrebari`.`Status` AS `Status`,`parajurist`.`intrebari`.`IdCategorie` AS `IdCategorie`,`parajurist`.`intrebari`.`DataAdresare` AS `DataAdresare`,`parajurist`.`intrebari`.`Comments` AS `Comments`,`parajurist`.`intrebari`.`CuvCheie` AS `CuvCheie` from (`parajurist`.`intrebari` join `parajurist`.`intrebaricategorii` on((`parajurist`.`intrebaricategorii`.`IdCategorie` = `parajurist`.`intrebari`.`IdCategorie`))) where (`parajurist`.`intrebari`.`Status` <> 0) order by (case when (`parajurist`.`intrebari`.`Status` = 2) then 1 else 2 end),`parajurist`.`intrebari`.`IdIntrebare` desc and codeigniter code:
$this->db->limit($start, $stop); $this->db->select('*'); $this->db->select('LEFT(intrebari_view.Titlu, 50) as Titlu'); $this->db->select('LEFT(intrebari_view.Descriere, 150) AS Descriere'); $this->db->join('IntrebariCategorii', 'IntrebariCategorii.IdCategorie = intrebari_view.IdCategorie'); $this->db->where('IntrebariCategorii.NumeCategorie', $cat); $this->db->from('intrebari_view'); $query = $this->db->get();
CASE.