Welcome Guest, Not a member yet? Register   Sign In
'OR Like' MySQL - Maybe a bug?
#1

[eluser]N3XT0R[/eluser]
Hello guys,
i am trying currently to get a searching result with a query created by the active records of CI.

here is the sourcecode part that i have implemented:
Code:
$this->db->select("portierung.portid, status, IFNULL(portierungstermin, '-') as portierungstermin, portierungskennung, carriername, IF(msncancel=1,'Ja','Nein') as msncancel, vorname, nachname, type", FALSE);
        $this->db->from("portierung");
        $this->db->join("portierungskennung", "portierung.dnr = portierungskennung.dnr");
        $this->db->join("portiernummern", "portierung.portid = portiernummern.portid", "LEFT");
        $this->db->join("subscriber", "subscriber.subid = portierung.subid");
        $this->db->join("carrier", "portierungskennung.cid = carrier.cid");
        
        if($trunk !== NULL){
            $this->db->join("trunks", "trunks.trunkid = subscriber.trunkid");
            $this->db->where("domain", $trunk);
        }
        
        if($query !== NULL){
            $this->db->or_like("vorname", $query); // problem is here.
            $this->db->like("nachname", $query);
            $this->db->like("strasse", $query);
            $this->db->like("wohnort", $query);
            $this->db->like("firma", $query);
            $this->db->like("e164", $query);
            
        }
        
        $this->db->order_by("portid", "DESC");
        $this->db->order_by("portierungstermin", "DESC");
        
        if($limit !== NULL && $offset !== NULL){
            $this->db->limit($limit, $offset);
        }
        
        /* @var $query CI_DB_RESULT */
        $query = $this->db->get();

So i am trying to get after the first "where" statement a "or_like" statement.
The result of the above sourcecode is:

Code:
SELECT portierung.portid, status, IFNULL(portierungstermin, '-') as portierungstermin, portierungskennung, carriername, IF(msncancel=1, 'Ja', 'Nein') as msncancel, vorname, nachname, type
FROM (`portierung`)
JOIN `portierungskennung` ON `portierung`.`dnr` = `portierungskennung`.`dnr`
LEFT JOIN `portiernummern` ON `portierung`.`portid` = `portiernummern`.`portid`
JOIN `subscriber` ON `subscriber`.`subid` = `portierung`.`subid`
JOIN `carrier` ON `portierungskennung`.`cid` = `carrier`.`cid`
JOIN `trunks` ON `trunks`.`trunkid` = `subscriber`.`trunkid`
WHERE `domain` = 'sip.xyz.example'
AND `vorname` LIKE '%m%' // why is here a "and", when i called the "or_like" method?
AND `nachname` LIKE '%m%'
AND `strasse` LIKE '%m%'
AND `wohnort` LIKE '%m%'
AND `firma` LIKE '%m%'
AND `e164` LIKE '%m%'
ORDER BY `portid` DESC, `portierungstermin` DESC
LIMIT 10

Now im a little bit confused, because i wrote the "or_like" statement after the where statement in my sourcecode.
CI seems to ignore this line and compile the Active record with a "like" and not a "OR xyz LIKE '%m%'".
But i would need a "or_like" to run my query successfull.

Yep, i can take the standard query to solve the problem. But maybe there are a more ideal solution.

I am using the Version 2.1.3.


#2

[eluser]N3XT0R[/eluser]
I got a solution, maybe not the best, but currently i think is every solution better:

CI_DB_active_record Class changes:
Code:
//Declaring and initialization of $ar_or_like
after Line 35 added.
var $ar_or_like                         = array();

//Adding array to the Reset-Function for Select.
after line 2032 added (Reset-Funktion):
'ar_or_like'                    => array(), // Zeile eingefügt.

//Adding array to the Reset-Function for write.
after line 2002 added:
'ar_or_like'                    => array(), // Zeile eingefügt.


after line  617 added:
array_push($this->ar_or_like, array($field, $match));

/**
* Check if or_like is set and replace the first "AND" (which will be everytime set
* when more than 0 like are setted too) with "OR", when ar_or_like is higher than 0.
*/
Line 1746 -  1754 replaced with:
if (count($this->ar_like) > 0 && count($this->ar_or_like) == 0)
{
if (count($this->ar_where) > 0)
{
  $sql .= "\nAND ";
}elseif(count($this->ar_or_like) > 0){
  $sql .= "\nOR ";
}

$sql .= implode("\n", $this->ar_like);
}
Notice:
Only done changes for select, this solution is not working for replace into or anything else.
Maybe it is helpful for another person, too.
#3

[eluser]Syllean[/eluser]
The user guide says that or_like() is identical to like() except that multiple instances are joined by OR, you didn't have a like first so the first instance is joined with and.
#4

[eluser]jvicab[/eluser]
This is due to CI doesn't enclose the items in OR section between parenthesis so if the query has AND or other statements the operator's precedence is not what we want.
I have two solutions for this, which I have posted on my blog (www.jvicab.com/blog/articledetails/12).
One of them is adding two functions to DB_active_rec.php (one to deal with or_where and another for or_like. both have the same problem) which properly encloses the OR items. The other is to use native CI where() (or like()) function to build the query as we want.
This is the functions I added to my DB_active_rec.php:
Code:
function like_brackets()
  {
    end($this->ar_like);
    $key = key($this->ar_like);
    // add a bracket close
    $this->ar_like[$key] .= ')';

    // add a bracket open
    reset($this->ar_like);
    $key = key($this->ar_like);
    $this->ar_like[$key] = '('.$this->ar_like[$key];

    // update the AR cache clauses as well
    if ($this->ar_caching === TRUE)
       $this->ar_cache_like[$key] = $this->ar_like[$key];
    return $this;
  }
  
  function where_brackets()
  {
    end($this->ar_where);
    $key = key($this->ar_where);
    // add a bracket close
    $this->ar_where[$key] .= ')';

    // add a bracket open
    reset($this->ar_where);
    $key = key($this->ar_where);
    $this->ar_where[$key] = '('.$this->ar_lar_whereke[$key];

    // update the AR cache clauses as well
    if ($this->ar_caching === TRUE)
       $this->ar_cache_where[$key] = $this->ar_where[$key];
    return $this;
  }

If you need more info, you can visit the ULR I provided or email me.




Theme © iAndrew 2016 - Forum software by © MyBB