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


Messages In This Thread
Can the Query Builder use RLIKE - by jLinux - 09-27-2015, 11:40 AM
RE: Can the Query Builder use RLIKE - by jLinux - 09-27-2015, 12:01 PM
RE: Can the Query Builder use RLIKE - by Narf - 09-27-2015, 12:20 PM
RE: Can the Query Builder use RLIKE - by jLinux - 09-27-2015, 12:35 PM
RE: Can the Query Builder use RLIKE - by Narf - 09-27-2015, 03:29 PM
RE: Can the Query Builder use RLIKE - by jLinux - 09-27-2015, 03:44 PM
RE: Can the Query Builder use RLIKE - by Narf - 09-28-2015, 02:51 AM



Theme © iAndrew 2016 - Forum software by © MyBB