Welcome Guest, Not a member yet? Register   Sign In
Can the Query Builder use RLIKE
#1
Question 

Is there a way to have an RLIKE statement in my query using query builder? I have a very dynamic query that im creating that sometimes requires a regex match in the WHERE statements, and I didnt see a way to do this with the query builder, so I just created the query manually, and man.. is it ugly! (Below)... So I was just asking if theres a way to do it the right way. Thanks!

PHP Code:
<?php
// Get the info for the current partition (such as the fields it
// contains, which represent the columns in the table
$partition $this->get_partition();

// The search elements are sent via POST, The KEY is the
// column name, and the Value is the value to filter by
$filter $this->input->post();

// Get the available coluns for a table, since they can be dynamic
$columns $this->get_columns();

// Construct the full table name were querying against
$table_name $this->_p(PARTITION_PREFIX self::$table_name);

// Creator Subquery string
$creator_query self::$db
    
->select('username')
    ->
from('accounts')
    ->
where('account_id'"{$table_name}.creator")
    ->
get_compiled_select();

// Modifier Subquery string
$modifier_query self::$db
    
->select('username')
    ->
from('accounts')
    ->
where('account_id'"{$table_name}.modifier")
    ->
get_compiled_select();

// Create an array of the select elements
$select = array(
    
$table_name.'.*',
    
"({$creator_query}) AS creator_username",
    
"({$modifier_query}) AS modifier_username"
);

// The where array gets constructed, then imploded later
$where = array();

// Some field types need special treatment.. 
foreach($partition->fields as $field)
{
    
// Is this field being filtered in the POST?
    
if(isset($filter[$field->column_name]))
    {
        switch(
$field->type)
        {
            
// Multiselects use RLIKE
            
case 'multiselect':
                if(
is_array($filter[$field->column_name]))
                    
$where[$field->column_name][] = $field->column_name " RLIKE '(" implode('|'$filter[$field->column_name]) . ")'";
                else
                    
$where[$field->column_name][] = $field->column_name " LIKE '%" $filter[$field->column_name] . "%'";
                break;

            
// Boolean/Checkbox
            
case 'boolean':
                
// Skip if array, array means its both yes and no, so same as unset
                
if( ! is_array($filter[$field->column_name]))
                    
$where[$field->column_name] = $field->column_name " = '" $filter[$field->column_name]  . "'";
                break;

            
// Textarea, string, etc
            
default:
                if(
is_array($filter[$field->column_name]))
                    foreach(
$filter[$field->column_name] as $k => $f)
                    {
                        
$f str_replace('*','%'$f);

                        if(
$k === 0)
                            
$where[$field->column_name][] = $field->column_name " LIKE '%" str_replace('*','%'$f ) . "%'";
                        else
                            
$where[$field->column_name][] = "OR " $field->column_name " LIKE '%" str_replace('*','%'$f ) . "%'";
                    }
                else
                    
$where[$field->column_name][] = $field->column_name " LIKE '%" str_replace('*','%'$filter[$field->column_name] ) . "%'";
                break;
        }
    }
}


$where_str '';


$i 0;

foreach(
$where as $column => $w)
{
    if(
$i++ > 0)
        
$where_str .= ' AND ';

    if(
is_array($w))
    {
        
$where_str .= '(';

        foreach( 
$w as $w2 )
            
$where_str .= {$w2}";

        
$where_str .= ')';
    }
    else
    {
        
$where_str .= {$w}";
    }
}

$query_str 'SELECT ' implode(','$select) . " FROM {$table_name} WHERE {$where_str}";

$query self::$db->query($query_str); 
Reply
#2

(This post was last modified: 09-27-2015, 12:34 PM by jLinux.)

Im trying to extend the query builder class and add MY_DB_query_builder::rlike and MY_DB_query_builder::_rlike methods that are very similar to the CI_DB_query_builder::like and CI_DB_query_builder::_like methods, heres the code thus far

PHP Code:
<?php defined('BASEPATH') OR exit('No direct script access allowed');

class 
MY_DB_query_builder extends CI_DB_query_builder
{
 
   public function rlike($field$match$escape NULL)
 
   {
 
       return $this->_rlike($field$match'AND '''$escape);
 
   }

 
   protected function _rlike($field$match ''$type 'AND '$not ''$escape NULL)
 
   {
 
       if ( ! is_array($field))
 
       {
 
           $field = array($field => $match);
 
       }

 
       is_bool($escape) OR $escape $this->_protect_identifiers;

 
       foreach ($field as $k => $v)
 
       {
 
           $prefix = (count($this->qb_where) === && count($this->qb_cache_where) === 0)
 
               $this->_group_get_type('') : $this->_group_get_type($type);

 
           $v $this->escape_like_str($v);

 
           $like_statement "{$prefix} {$k} {$not} RLIKE '{$v}'";

 
           // some platforms require an escape sequence definition for LIKE wildcards
 
           if ($this->_like_escape_str !== '')
 
           {
 
               $like_statement .= sprintf($this->_like_escape_str$this->_like_escape_chr);
 
           }

 
           $this->qb_where[] = array('condition' => $like_statement'escape' => $escape);
 
           if ($this->qb_caching === TRUE)
 
           {
 
               $this->qb_cache_where[] = array('condition' => $like_statement'escape' => $escape);
 
               $this->qb_cache_exists[] = 'where';
 
           }
 
       }

 
       return $this;
 
   }
}

/* File: application/database/MY_DB_query_builder.php */ 


Now am I wrong in assuming that should be located at  application/database/MY_DB_query_builder.php? Because the error i'm getting is:Message: Call to undefined method CI_DB_mysqli_driver::rlike()
Reply
#3

You're wrong in assuming that ... kind of. The database classes aren't extensible at all, so it doesn't matter where you'd put it.
Reply
#4

(09-27-2015, 12:20 PM)Narf Wrote: You're wrong in assuming that ... kind of. The database classes aren't extensible at all, so it doesn't matter where you'd put it.

Oh, ok.. Is there a way to accomplish this then? I don't want to just throw it into the DB_query_builder.php file, I know id get crucified for that, and it would make updates a PITA
Reply
#5

I'm not sure what you mean by "this" here, but either way I'd just use a raw query(). Whatever else you could do isn't worth the effort and if you're concerned about it being "ugly", well ... regular expressions are ugly by nature and that's unavoidable.
Reply
#6

(09-27-2015, 03:29 PM)Narf Wrote: I'm not sure what you mean by "this" here, but either way I'd just use a raw query(). Whatever else you could do isn't worth the effort and if you're concerned about it being "ugly", well ... regular expressions are ugly by nature and that's unavoidable.

This = The question in my original post, (use RLIKE via the Query Builder)

This really isnt limited to RLIKE for me, theres a few other queries I wanted to create using the query builder, but CI's query builder didnt have the feature I was looking for.

The Query Builder is pretty awesome really, its got some pretty neat stuff that I hadnt even thought of using before. Im very surprised there isn't a way to extend the class to add more functionality to it.
Reply
#7

(09-27-2015, 03:44 PM)jLinux Wrote:
(09-27-2015, 03:29 PM)Narf Wrote: I'm not sure what you mean by "this" here, but either way I'd just use a raw query(). Whatever else you could do isn't worth the effort and if you're concerned about it being "ugly", well ... regular expressions are ugly by nature and that's unavoidable.

This = The question in my original post, (use RLIKE via the Query Builder)

No, there's no 'rlike' command in the query builder ... You could probably use where("foo RLIKE 'expression'"), but at that point you're writing raw SQL and therefore QB is the wrong thing to use.

(09-27-2015, 03:44 PM)jLinux Wrote: This really isnt limited to RLIKE for me, theres a few other queries I wanted to create using the query builder, but CI's query builder didnt have the feature I was looking for.

The Query Builder is pretty awesome really, its got some pretty neat stuff that I hadnt even thought of using before. Im very surprised there isn't a way to extend the class to add more functionality to it.

Do you plan on moving from e.g. MySQL to Postgres? Is there even a tiny chance that this would ever happen with your project? Or does your application just need to be able to run on different databases depending on user configuration?

If the answer to all of these is 'No', then don't use the Query Builder.
If the answer to at least one of them is 'Yes', then QB is indeed helpful. However, Postgres doesn't support RLIKE (and I believe only MySQL does), so you shouldn't be using RLIKE in the first place. If the DB classes were extensible, that would allow you to shoot yourself in the foot with that.

It doesn't matter how awesome you may think a tool is. You don't use tools for awesomeness, you use them when they're helpful.
If you had a Ferrari, would you drive it for every 100 meters that you could otherwise walk? I don't think so, and this here is the same thing.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB