Parenthesis Support

[sasan]

This patch will help you to put parentheses in your SQL queries. Imagine the following query:

SELECT `username`, `lastname`, `appname`, `id`, `name` ,`group_name` FROM arag_users JOIN
`arag_groups` ON arag_groups.id = arag_users.group_id WHERE `group_name` LIKE '%admin%' AND
(username LIKE '%admin%' OR name LIKE '%admin%' OR lastname LIKE '%admin%') AND `appname` =
'arag' ORDER BY `appname` ASC, `lastname` ASC, `group_name` ASC

In the above example you can make groups of your where conditions that return a whole boolean result. In this way you can have more flexible where or like clauses. like the following:

if ($user != NULL) {
    $row = explode(" ", $user);
    foreach ($row as $tag) {
        $this->db->like('(username', $tag);
        $this->db->orlike($this->tablePrefix.$this->tableNameUsers.".name", $tag);
        $this->db->orlike('lastname)', $tag);
    }
}

if ($appName != NULL) {
    if ($flagappname) {
        $this->db->like('appname', $appName);
    } else {
        $this->db->where('appname', $appName);               
    }
}

You can even use operators and there will be no conflict, e.g. :

where('id>)', $id)

[zombor]

Support for this should be Database wide, not coded in the driver I think. Need to investigate further.

[Shadowhand]

Please review sasan's patch and update ticket as necessary.

[Shadowhand]

Patch removed, it is not longer valid based on current SVN.

[allain]

This seems really hackish to me.

I'm not saying it's a bad idea, just that there might be a cleaner way of implenting it.

[shaun]

This is how I implemented it:

	public function join($table, $cond, $type = '')
	{
		if ($type != '')
		{
			$type = strtoupper(trim($type));

			if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE))
			{
				$type = '';
			}
			else
			{
				$type .= ' ';
			}
		}
		
		if(preg_match_all('/\s+(AND|OR)\s+/', $cond, $matches)) {
			$arr = preg_split('/\s+(AND|OR)\s+/', $cond);
			$cond = "(";
			foreach($arr as $k=>$v) {
				if (preg_match('/([a-z0-9.].+)(=|\s+=\s+)([a-z0-9.].+)/i', $v, $where)) {
					$cond .= $this->driver->escape_column($this->config['table_prefix'].$where[1]).
							' = '.
							(is_numeric($where[3]) ? $where[3] : $this->driver->escape_column($this->config['table_prefix'].$where[3])).$matches[0][$k];
				} else {
					Log::add('debug', 'Failed to add join: '.$v);
				}
			}
			$cond .= ")";
		} else {
			if (preg_match('/([a-z0-9.].+)(=|\s+=\s+)([a-z0-9.].+)/i', $cond, $where)) {
				$cond = $this->driver->escape_column($this->config['table_prefix'].$where[1]).
						' = '.
						(is_numeric($where[3]) ? $where[3] : $this->driver->escape_column($this->config['table_prefix'].$where[3]));
			} else {
				Log::add('debug', 'Failed to add join: '.$cond);			
			}
		}

		$this->join[] = $type.'JOIN '.$this->driver->escape_column($this->config['table_prefix'].$table).' ON '.$cond;

		return $this;
	}

[Shadowhand]

#553 is related to this.

[Shadowhand]

#416 is related to this.

[Shadowhand]

#670 is related to this.

[Shadowhand]

#545 is related to this.