OR vs. AND in Active Record LIKE query

OR vs. AND in Active Record LIKE query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: OR vs. AND in Active Record LIKE query (/showthread.php?tid=40439)

OR vs. AND in Active Record LIKE query - El Forum - 04-08-2011

[eluser]bhumes[/eluser]
Trying to use Active Record to achieve this SQL call:

Code:
select * from table where id = '130' OR logics LIKE ('130')

Using:

Code:
$cid = 130;

$this->db->where('id',$cid);

$this->db->or_like('logics',$cid);

But I get:

Code:
select * from table where id = '130' AND logics LIKE ('130') <-- AND, not OR

I've located the offending code in DB_active_rec.php (lines 1765-1775)

Code:
// Write the "LIKE" portion of the query

if (count($this->ar_like) > 0)

{

  if (count($this->ar_where) > 0)

  {

    $sql .= "\nAND ";

  }

  $sql .= implode("\n", $this->ar_like);

}

The ar_like array does not contain any information to suggest that an 'OR' was used. The array simply contains ''logics' LIKE ('130')", and since there are rows in the ar_where array, the 'AND' gets written.

Is this a bug, or is this by design? Please let me know if I can provide further information.

Thanks!

OR vs. AND in Active Record LIKE query - El Forum - 04-09-2011

[eluser]web-johnny[/eluser]
As I know the logic is :
if you write
$this->db->where(...);
$this->db->or_where(...);
it will have WHERE ... OR WHERE ....
But if you write just
$this->db->or_where(....);
It will have the result WHERE .... (and not or where)
The same thing is for the like or like.
When you put
$this->db->where(...);
$this->db->or_where(...);
$this->db->or_where(...);
$this->db->or_like(...);
$this->db->or_like(...);
You will have the result WHERE .... OR WHERE .... OR WHERE .... AND LIKE .... OR LIKE ....
I'm not sure but I think this is not a bug. I usually work with
$this->db->where("sting with all the where or and etc"); Just to be sure that everything works correctly.

Hope this helps

OR vs. AND in Active Record LIKE query - El Forum - 04-09-2011

[eluser]guidorossi[/eluser]
I think web-johnny is right, the way of do it is:

Code:
$cid = 130;

$this->db->where('id',$cid);

$this->db->or_where('logics LIKE',$cid);

OR vs. AND in Active Record LIKE query - El Forum - 04-09-2011

[eluser]osci[/eluser]
I also use $this->db->where(“sting with all the where or and etc”); but this is because of not being able to accomplish exactly the query you want when you have such an occasion. So I would consider it a bug because I should be given the option of Where something or like something and not be forced to an AND.

In _like function

Code:
$prefix = (count($this->ar_like) == 0) ? '' : $type;

So the first time it is called it doesn't add a prefix intentionally so as to follow in function _compile_select

Code:
if (count($this->ar_like) > 0)

{

  if (count($this->ar_where) > 0)

  {

    $sql .= "\nAND ";

  }

  $sql .= implode("\n", $this->ar_like);

}

I don't know if it's a dirty fix but consider

In _like function

Code:
$prefix = $type;

and in _compile_select

Code:
if (count($this->ar_like) > 0)

{

  if (count($this->ar_where) > 0)

  {

    $sql .= implode("\n", $this->ar_like);

  } else {

    $dummy = implode("\n", $this->ar_like);

    $sql .= substr($dummy,0,strpos($dummy, ' '));

  }

}

That would give correct results for

Code:
$this->db->where('id',$cid);

$this->db->or_like('logics',$cid);

OR vs. AND in Active Record LIKE query - El Forum - 04-11-2011

[eluser]smartweb[/eluser]
$cid = 130;
$this->db->where('id',$cid);
$this->db->or_where('logics LIKE',"%,".$cid.",%")

OR vs. AND in Active Record LIKE query - El Forum - 04-12-2011

[eluser]osci[/eluser]
@smartweb That's a workaround, but still

this

Code:
$this->db->where('id',$cid);

$this->db->like('logics',$cid);

should produce WHRE id = .... AND like

while this

Code:
$this->db->where('id',$cid);

$this->db->or_like('logics',$cid);

should produce WHRE id = .... OR like

I'm not using like() or or_like() because of this bug.
Of course it is not a priority fix but it should be corrected.

OR vs. AND in Active Record LIKE query - El Forum - 12-26-2012

[eluser]davlyn[/eluser]
It might not interest many, but for this I use a "trick" that doesn't hack the files.

The idea is to use a them before hand, so that if either value comes it will be or.

Code:
$this->db->where('field_1', 'sa9029s0aq2');

$this->db->like('field_1','sa9029s0aq2, 'none');

Where sa9029s0aq2 is kind of a pointless random string that has very very low probability of being true.

This way any next or_where or or_like will work. It is kinda a stupid solution, but it may serve (and save from frustration) for a quick fix.

EDIT: disregard this, smartweb's is the way to go.