CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
Kanishka Panamaldeniya
Kanishka Panamaldeniya

Reputation: 17586

why can't i use parentheses in codeigniter join query

hi i am using codeigniter join 

$this->db->join

i used 

$this->db->join ( $table2 . " as c","( c.3rdPartyID=a.ShopID OR c.3rdPartyID=a.ParentID OR c.ShopID=a.ShopID OR c.ShopID=a.ParentID )  AND c.3rdPartyID !=0 ", 'left' );

and i also tried with 

$join_query = "( c.3rdPartyID=a.ShopID OR c.3rdPartyID=a.ParentID OR c.ShopID=a.ShopID OR c.ShopID=a.ParentID )  AND c.3rdPartyID !=0 ";

$this->db->join ( $table2 . " as c",$join_query, 'left' );

it gives an error .

but with out parentheses it works fine , like this 

$this->db->join ( $table2 . " as c"," c.3rdPartyID=a.ShopID OR c.3rdPartyID=a.ParentID OR c.ShopID=a.ShopID OR c.ShopID=a.ParentID   AND c.3rdPartyID !=0 ", 'left' );

but in my case i need parentheses , should i go to normal mysql queries , is codeignier incapable of doing this , please help...

this is the error

enter image description here

Upvotes: 2

Views: 1052

Answers (4)

Kaniyan Tesla
Kaniyan Tesla

Reputation: 1

Try this one

    $this->db->from ( $table2 . " as c"," join on ( c.3rdPartyID=a.ShopID OR c.3rdPartyID=a.ParentID OR c.ShopID=a.ShopID OR c.ShopID=a.ParentID )  AND c.3rdPartyID !=0 ");

Upvotes: 0

Bruno
Bruno

Reputation: 101

Use false. Ex: 

$this->db->join ( $table2 . " as c","( c.3rdPartyID=a.ShopID OR c.3rdPartyID=a.ParentID OR c.ShopID=a.ShopID OR c.ShopID=a.ParentID )  AND c.3rdPartyID !=0 ", 'left', false );

Upvotes: 10

Code Prank
Code Prank

Reputation: 4250

Copied from ellislab forum:

That query will not be possible using the Active Record join() function. There is a line of regular expressions that looks for a certain structure of a join condition (table.something = table.somethingelse typically). It will ignore any characters other than alphanumeric and periods (.) when recreating the first part of the join statement you provide. Part of the reason of this is Active Record employs a private function called _protect_identifiers, which adds any necessary info to a column name (hostname.database.table.column) in the case that it is required (see file ‘DB_driver.php’ inside the Database system folder for a better description). Adding in additional characters before the table.column would cause those characters to be in the middle of the column name structure, in the event a host or database name need to be added to it.

So basically, you can’t use the join() function as it stands. You can use the query() function and write it out fully yourself, or extend the Active Record class and rewrite the join() function to suit your needs.

Upvotes: 0

jmadsen
jmadsen

Reputation: 3675

Either

1) move your "AND c.3rdPartyID !=0" to a separate ->where()

or

2) write your query & use:

$sql = "Select...";
$this->db->query($sql);

Upvotes: 0

Related Questions