Active Record "where()->or_like()" generates "AND LIKE" not "OR LIKE"!

[eluser]TomsB[/eluser]

Code:

$query = $this->db->select('id')
    ->where_in('id',array(5,3,6,7))
    ->or_like('name','test')
    ->or_like('name','test2')
    ->get('table');
echo $this->db->last_query();

This generates:
SELECT `id` FROM (`table`) WHERE `id` IN (5, 3, 6, 7) AND `name` LIKE '%test%' OR `name` LIKE '%test2%'
But I need to get:
SELECT `id` FROM (`table`) WHERE `id` IN (5, 3, 6, 7) OR `name` LIKE '%test%' OR `name` LIKE '%test2%'

I'm stuck with this for about two hours now.. Is this even possible to get "OR"?

[eluser]adamfairholm[/eluser]
I could be proven wrong, but I don't believe CI's Active Record can do that. I've taken a look at the active record class, and I haven't found a place where there is a provision for that.

You could try Ignited Query, which has the ability to handle nested WHERE statements, and probably can handle the nested WEHRE/LIKE statements the way you'd like.

[eluser]xwero[/eluser]
which CI version are you using?

[eluser]TomsB[/eluser]
version = 1.7.0

[eluser]xwero[/eluser]
what do you get if you do this

Code:

$this->db->select('id');
$this->db->where_in('id',array(5,3,6,7));
$this->db->or_like('name','test');
$this->db->or_like('name','test2');
print_r($this->db->ar_like);
$query = $this->db->get('table');

[eluser]TomsB[/eluser]
I get:

Code:

Array
(
    [0] =>  `name`  LIKE '%test%'
    [1] => OR  `name`  LIKE '%test2%'
)

I am experimenting with this right now:

Code:

...
$where = implode("\n", $this->db->ar_where);
$this->db->_reset_select();
...
$like = implode("\n", $this->db->ar_like);
$this->db->_reset_select();
...
$query = $this->db->query('SELECT id
         FROM table WHERE '.$where.' OR '.$like);
...

Am I on the right track?

[eluser]xwero[/eluser]
The problem is that the first item no OR has which means an AND is added. This is a bug in the AR library.

Code:

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

That line in the _like method is giving you headaches. If that is removed and in the _compile_select method

Code:

if (count($this->ar_like) > 0)
        {
            if (count($this->ar_where) > 0)
            {
                $sql .= "\nAND ";
            }

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

changes to

Code:

if (count($this->ar_like) > 0)
        {
            if (count($this->ar_where) == 0)
            {
                $this->ar_like[0] = str_replace(array('OR','AND'),'',$this->ar_like[0]);
            }

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

Everything should work and your headache will be over.

[eluser]TomsB[/eluser]
With changes you suggested I get:
SELECT `id` FROM (`table`) WHERE `id` IN (5, 3, 6, 7) `name` LIKE ‘%test%’ OR `name` LIKE ‘%test2%’
There is no OR or AND:
`id` IN (5, 3, 6, 7) OR|AND `name` LIKE ‘%test%’

[eluser]xwero[/eluser]
What is this output

Code:

$this->db->select('id');
$this->db->where_in('id',array(5,3,6,7));
print_r($this->db->ar_where);
$this->db->or_like('name','test');
$this->db->or_like('name','test2');
print_r($this->db->ar_like);
$query = $this->db->get('table');

It seems from your test the ar_where is empty but you did add a where part method.

[eluser]TomsB[/eluser]

Code:

Array
(
    [0] => `id` IN (5, 3, 6, 7)
)
Array
(
    [0] =>  `name`  LIKE '%test%'
    [1] => OR  `name`  LIKE '%test2%'
)

Btw, I got error when applied your suggested changes to DB_active_rec.php
Message: Undefined variable: prefix

If I execute this code with changed DB_active_rec.php, I get the same problem - without OR or AND.