phpsqlcodeigniteractiverecordquery-builder

Convert SQL containing GROUP BY and SUM() to active record in CodeIgniter

SELECT *, SUM(`Relaxation_rating` + `Food_rating`) AS Ratings
GROUP BY CODE
ORDER BY Ratings DESC
FROM destinations

How do I convert this sql code to CctiveRecord in CodeIgniter?

I tried the following method calls.

$this->db->select("'(SELECT *, SUM(Relaxation_rating + Food_rating) FROM database GROUP BY CODE') AS Ratings", FALSE);
$this->db->get("database");

The SQL from that code is invalid and of course doesn't provide any results:

SELECT '(SELECT *, SUM(Relaxation_rating + Food_rating) FROM database GROUP BY CODE') AS Ratings
FROM `database`
Solution

  • Try:

    $data = $this->db->select('*, SUM(`Relaxation_rating` + `Food_rating`) AS Ratings', false)->group_by('CODE')->order_by('Ratings', 'desc')->get('destinations')->result_array();

    Or,

    $this->db->select('*, SUM(`Relaxation_rating` + `Food_rating`) AS Ratings', false)->group_by('CODE')->order_by('Ratings', 'desc')->get('destinations')->result();