Welcome Guest, Not a member yet? Register   Sign In
After upgrade from 1.7.2 to 2.1, LEFT OUTER JOIN mysql query throws a syntax error
#1

[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` ASC

Notice 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');
   }
#2

[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.
#3

[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.
#4

[eluser]Webnet[/eluser]
This issue still exists.... //bumped
#5

[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.
#6

[eluser]Webnet[/eluser]
Thanks a lot, I posted it there!




Theme © iAndrew 2016 - Forum software by © MyBB