How to convert this query in CodeIgniter 3

Question

I have got this piece of snippet from: http://sqlfiddle.com/#!9/a9ec7e/4/0

What it does is it gets all the data from the table, All duplicate rows will be counted as 1 and lastly get only the latest data that ID/Item.

SELECT
        p.*
    FROM
        (
          -- For every product_id, find maximum created_at time
          SELECT
              product_id, max(created_at) AS created_at
          FROM 
              purchases
          GROUP BY
              product_id
         ) AS mx 
         -- JOIN to the whole table
         JOIN purchases p ON
              mx.product_id = p.product_id AND mx.created_at = p.created_at
    ORDER BY
         product_id ;

Here's the picture of the result from the snippet above. Picture of the result (image):

The real question is, how can I make it work with CodeIgniter3 query? Or How can I do this query in CodeIgniter3.

I tried using $this->db->query(); but no luck. Here's the image of my take using the snippet above and changed the table name, column. Result of my take (image):

Answer 1

try this :

$query = $this->db->query("SELECT p.* FROM (SELECT product_id, max(created_at) AS created_at FROM purchases GROUP BYproduct_id) AS mx 
     JOIN purchases p ON mx.product_id = p.product_id AND mx.created_at = p.created_at ORDER BY product_id");

return $query->result();

hope this helped