Convert MySQL query including a LEFT JOINed subquery and a CASE expression to CodeIgniter

I'm working on an ecommerce application. I have a raw SQL query that I'd like to convert to CodeIgniter.

This is my query:

SELECT
    products.product_name,
    products.product_id,
    products.short_description,
    pi.img,
    CASE
        WHEN products.sp_price = 0 THEN products.price
        WHEN products.sp_price != 0 THEN products.sp_price
    END as pprice
FROM 
    (`offers_products`)
JOIN `products` ON `offers_products`.`product_id` = `products`.`product_id`
LEFT JOIN (
    SELECT image_name as img, product_id as pid
    from product_images
    GROUP BY pid
)pi ON `products`.`product_id` = `pi`.`pid`

How do I convert this to a CodeIgniter query?

There is no need to convert your query. And also the is no rule that you should use codeigniter query only.

you can use

$res = $this->db->query("your query here")->result();

$res will have that result() you want. This will help you.

For more reference, check here