Bug Report #204

Parenthesis Support

Added by Sasan Rose - over 4 years ago. Updated over 3 years ago.

Status:Closed Start date:
Priority:Urgent Due date:
Assignee:Woody Gilk % Done:

0%
Category:Libraries:Database
Target version:2.3
Resolution:fixed Points:

Description

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)

History

#1
Updated by Jeremy Bush over 4 years ago

  • Status changed from New to Assigned

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

#2
Updated by Woody Gilk over 4 years ago

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

#3
Updated by Woody Gilk over 4 years ago

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

#4
Updated by Allain Lalonde over 4 years ago

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.

#5
Updated by Shaun Harrison - about 4 years ago

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'].$wherer1).
                            ' = '.
                            (is_numeric($wherer3) ? $wherer3 : $this->driver->escape_column($this->config['table_prefix'].$wherer3)).$matchesr0[$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'].$wherer1).
                        ' = '.
                        (is_numeric($wherer3) ? $wherer3 : $this->driver->escape_column($this->config['table_prefix'].$wherer3));
            } 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;
    }

#6
Updated by Woody Gilk almost 4 years ago

#553 is related to this.

#7
Updated by Woody Gilk almost 4 years ago

#416 is related to this.

#8
Updated by Woody Gilk almost 4 years ago

#670 is related to this.

#9
Updated by Woody Gilk almost 4 years ago

#545 is related to this.

#10
Updated by Woody Gilk over 3 years ago

  • Status changed from Assigned to Closed
  • Resolution set to fixed

This has been committed across several revisions.

#11
Updated by min - over 3 years ago

Where is the patch for this? Am I missing something completely?

#12
Updated by Woody Gilk over 3 years ago

This shouldn't have been marked as a patch, it's a feature request.

#13
Updated by Sasan Rose - over 3 years ago

I really thank you guys for finally closing this feature request. :D

Also available in: Atom PDF