+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Query Builder Help (/showthread.php?tid=66580)
Query Builder Help - Paul1972UK - 11-07-2016
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
RE: Query Builder Help - ramadhansutejo - 11-07-2016
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();
RE: Query Builder Help - Paul1972UK - 11-08-2016
(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(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