CodeIgniter Forums

Full Version: Query Builder Help
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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(30);
        
$this->db->get(); 
(11-07-2016, 07:43 PM)ramadhansutejo Wrote: [ -> ]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(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