+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error (/showthread.php?tid=48259)
After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error - El Forum - 01-10-2012
[eluser]Webnet[/eluser]
With 1.7.2 this query works, after upgrading to 2.1 this query throws a syntax error. It seems as though on the first ->join() listed below it's changing (cf.products_id = p.products_id to cf.products_id = p.products_id but it leaves the old parenthesis in place. The result query currently is...
Code:
...
LEFT OUTER JOIN `products_description` baseProductsDescription ON `baseProductsDescription`.`products_id` = IF(p.base_products_id, p.base_products_id, p.products_id)
LEFT OUTER JOIN `customers_favorites` cf ON `cf`.`products_id` = `p`.`products_id` AND cf.customers_id = 12)
LEFT OUTER JOIN `products` baseProducts ON `baseProducts`.`products_id` = IF(p.base_products_id, p.base_products_id, p.products_id)
LEFT OUTER JOIN `products_generation` pg ON `baseProducts`.`products_id` = `pg`.`products_id` AND pg.account_type_id = 1)
LEFT OUTER JOIN `customers` forc ON `forc`.`customers_id` = `p`.`cID` AND forc.customers_type_id = 4)
WHERE `p`.`products_status` = 1 AND `baseProducts`.`products_status` = 1 AND `cf`.`customers_id` = 12 ORDER BY `pd`.`products_description` ASCNotice all LEFT OUTER JOIN's have closing parenthesis but not an opening one.
A portion of my query which shows me including the opening parenthesis....
Code:
$this->db->select('baseProductsDescription.products_sku AS base_product_sku', FALSE);
$this->db->join(TABLE_PRODUCTS_DESCRIPTION.' baseProductsDescription', 'baseProductsDescription.products_id = IF(p.base_products_id, p.base_products_id, p.products_id)', 'LEFT OUTER');
$this->db->join(TABLE_CUSTOMERS_FAVORITES.' cf', '(cf.products_id = p.products_id AND cf.customers_id = '.$this->user->getCurrentUserProperty('id').')', 'LEFT OUTER');
// join the base products in, for determining the correct value for custom
$this->db->join(TABLE_PRODUCTS.' baseProducts', 'baseProducts.products_id = IF(p.base_products_id, p.base_products_id, p.products_id)', 'LEFT OUTER');
if(!empty($this->user->current->account_type) && !empty($this->user->current->account_type->id)) {
$this->db->select('IF(pg.generation_type, pg.generation_type, baseProducts.custom) AS `custom`', FALSE);
$this->db->select('IF(pg.generation_type AND pg.product_customizer_id, pg.product_customizer_id, baseProducts.product_customizer_id) AS `product_customizer_id`', FALSE);
$this->db->select('(IF(pg.generation_type, pg.generation_type, baseProducts.custom) = 0) AS `sort_custom`', FALSE);
$this->db->join('products_generation pg', '(baseProducts.products_id = pg.products_id AND pg.account_type_id = '.$this->user->current->account_type->id.')', 'LEFT OUTER');
$this->db->select('(IF(pg.generation_type IS NOT NULL, pg.generation_type, baseProducts.custom) = 0 OR (p.base_products_id != 0 AND pd.product_type = 1)) AS is_built', FALSE);
$this->db->select('(IF(pg.generation_type IS NOT NULL, pg.generation_type, baseProducts.custom) = 0) AS is_static', FALSE);
} else {
$this->db->select('baseProducts.custom');
$this->db->select('baseProducts.product_customizer_id');
$this->db->select("(baseProducts.custom = 0 AND coalesce(baseProducts.products_pdfupload, '') != '') AS `sort_custom`", FALSE);
$this->db->select("((baseProducts.custom = 0 AND coalesce(baseProducts.products_pdfupload, '') != '') OR (p.base_products_id != 0 AND pd.product_type = 1)) AS `is_built`", FALSE);
$this->db->select("(baseProducts.custom = 0 AND coalesce(baseProducts.products_pdfupload, '') != '') AS `is_static`", FALSE);
}
$this->db->select('baseProducts.license_id');
// add in state if available
if($this->user->national_account) {
$this->db->join(TABLE_CUSTOMERS.' forc', '(forc.customers_id = p.cID AND forc.customers_type_id = 4)', 'LEFT OUTER');
$this->db->select('forc.customers_firstname AS customer');
} else {
$this->db->join(TABLE_STATES.' st', 'st.states_id=p.for_state', 'LEFT OUTER');
$this->db->select('IF(st.states_id, st.states_displayname, \'\') AS state');
}After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error - El Forum - 04-10-2012
[eluser]Unknown[/eluser]
Yes, it's happening to me too. I'm trying to do this:
Code:
$this->db->join('table_a', ' ((table_b.key_id = table_a.key_id) AND (table_a.type = "my_type"))', 'left');You are right that the preg_match is breaking it. The match says...
Code:
preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match)... that we will split my ON clause into exactly 3 parts -- [letters, numbers, underscores, or periods] followed by [spaces or non-letter-number-underscores] followed by anything. In my case, the three parts are
Code:
1. table_b.key_id
2. =
3. table_a.key_id) AND (table_a.type = "my_type"))In other words, as written, the join command will ONLY work with simple ON clauses. Here's hoping they update the documentation to spell out the limitations of join.
After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error - El Forum - 04-10-2012
[eluser]Webnet[/eluser]
I found it odd that this happened in a later version of code igniter. We're unable to upgrade because of this limitation. We'd have to do some rewriting to make this work.
After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error - El Forum - 06-29-2012
[eluser]Webnet[/eluser]
This issue still exists.... //bumped
After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error - El Forum - 06-29-2012
[eluser]jmadsen[/eluser]
If you've confirmed this is a bug, the next step would be to report it here:
https://github.com/EllisLab/CodeIgniter/issues
This forum is linked to but doesn't serve as an Bugtracker - you won't necessarily get help or a solution here.
After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error - El Forum - 06-29-2012
[eluser]Webnet[/eluser]
Thanks a lot, I posted it there!