Query Builder Help

Hi All
Im not sure if this is the right place to post this, but I couldnt find a dedicated forum for database queries and Im New here!
Im using CI 3.06
Basically I need to know if the following query can be written in the query builder, Its taken me sometime to work out this exact query so I did it with the aid of PHPMyAdmin first so what ive got is just plain MySQL and I need to convert it to the query builder syntax.

I understand how the query builder works, for the most part but this is quite a complicated query with a sub query.
I think I will need to use get_compiled_select() to get the inner part first.

anyway here is the query, Please help me convert this to the query builder :

SELECT `id` , `title` , `image` , `category` , `price` ,  p.`brand` , p.`pattern` , LEFT( short_description, 140 ) AS description
FROM products AS p
LEFT JOIN `product_short_descriptions` ON `product_short_descriptions`.`product_id` = `id` 
JOIN ( SELECT p.brand, p.pattern, MIN( p.price ) AS min_price
FROM products AS p
JOIN product_to_category AS ptc ON p.id = ptc.product_id
WHERE ptc.category_id =1
AND price >= '102.30'
AND price <= '804.10'
AND STATUS =1
GROUP BY p.brand, p.pattern ) 
AS sq ON sq.brand = p.brand
AND sq.pattern = p.pattern
AND p.price = sq.min_price
GROUP BY p.brand, p.pattern
LIMIT 0 , 30

Im kinda stuck as to where I go about adding the bottom part AS sq ... etc..

Many Many Thanks Paul

try this 

PHP Code:

$this->db->select('`id` , `title` , `image` , `category` , `price` ,  p.`brand` , p.`pattern` , LEFT( short_description, 140 ) AS description');
        $this->db->from('products AS p');
        $this->db->join('`product_short_descriptions`', '`product_short_descriptions`.`product_id` = `id`', 'left');
        $this->db->join('( SELECT p.brand, p.pattern, MIN( p.price ) AS min_price
            FROM products AS p
            JOIN product_to_category AS ptc ON p.id = ptc.product_id
            WHERE ptc.category_id =1
            AND price >= '102.30'
            AND price <= '804.10'
            AND STATUS =1
            GROUP BY p.brand, p.pattern ) 
            AS sq ', 'sq.brand = p.brand AND sq.pattern = p.pattern AND p.price = sq.min_price');
        $this->db->group_by('p.brand, p.pattern');
        $this->db->limit(0 , 30);
        $this->db->get(); 


Caffeine IT Solutions | Bina Darma University

try this 

PHP Code:

$this->db->select('`id` , `title` , `image` , `category` , `price` ,  p.`brand` , p.`pattern` , LEFT( short_description, 140 ) AS description');
        $this->db->from('products AS p');
        $this->db->join('`product_short_descriptions`', '`product_short_descriptions`.`product_id` = `id`', 'left');
        $this->db->join('( SELECT p.brand, p.pattern, MIN( p.price ) AS min_price
            FROM products AS p
            JOIN product_to_category AS ptc ON p.id = ptc.product_id
            WHERE ptc.category_id =1
            AND price >= '102.30'
            AND price <= '804.10'
            AND STATUS =1
            GROUP BY p.brand, p.pattern ) 
            AS sq ', 'sq.brand = p.brand AND sq.pattern = p.pattern AND p.price = sq.min_price');
        $this->db->group_by('p.brand, p.pattern');
        $this->db->limit(0 , 30);
        $this->db->get(); 


Thanks very much I`ll try this and let you know if it works ok

Again Many Thanks for the reply!
Paul