Nontrivial active records question

[eluser]german.zvonchuk[/eluser]
The query itself is not complicated, I just wanted to write it using the Active records, without passing SQL code.

Using the array below, I need to make Active Records query, which will produce SQL code shown below.

Example array:

Code:

Array
(
    [id_item_type] => Array
        (
            [0] => 1
            [1] => 2
        )

    [id_street] => Array
        (
            [0] => 158
            [1] => 209
        )

    [id_item_target] => Array
        (
            [0] => 1
            [1] => 2
        )

)

Code:

SELECT COUNT(*) AS `numrows`
FROM (`item`)
WHERE
(`id_item_type` = '1'
OR `id_item_type` = '2')
AND
(`id_street` = '158'
OR `id_street` = '209')
AND
(`id_item_target` = '1'
OR `id_item_target` = '2')

[eluser]german.zvonchuk[/eluser]
I tried this code:

Code:

foreach($_POST as $key => $value_array) {
    foreach($value_array as $value) {
        $this->db->or_where($key, $value);
    }            
}
$this->db->from('item');
$this->db->count_all_results();

but it din't produced the result I need.

Result:

Code:

SELECT COUNT(*) AS `numrows`
FROM (`item`)
WHERE
`id_item_type` = '1'
OR `id_item_type` = '2'
OR `id_street` = '158'
OR `id_street` = '209'
OR `id_item_target` = '1'
OR `id_item_target` = '2'

[eluser]Cristian Gilè[/eluser]
Hi german.zvonchuk,

I don't test it but could work:

Code:

$skip = FALSE;
foreach($_POST as $key => $value_array) {
    foreach($value_array as $value) {
        if( ! $skip)
        {
         $this->db->where($key, $value);
         $skip = TRUE;
        }
        else
        {
         $this->db->or_where($key, $value);
         $skip = FALSE;
        }
    }            
}
$this->db->from('item');
$this->db->count_all_results();

[eluser]german.zvonchuk[/eluser]
Hi, Cristian Gilè.

Thank you, for the help. But I decided that using Active Records it's not possible to generate such a query.

Below is the query which is generated by Active Query using your method:

Code:

SELECT COUNT(*) AS `numrows`
FROM (`item`)
WHERE `id_item_type` = '1'
OR `id_item_type` = '2'
AND `id_item_target` = '1'

And this is what I want the query to be:

Code:

SELECT COUNT(*) AS `numrows`
FROM (`item`)
WHERE (`id_item_type` = '1' OR `id_item_type` = '2')
AND `id_item_target` = '1'

[eluser]Cristian Gilè[/eluser]
There is a proposal in the CI reactor forum for parenthses in active record.

In the mean time, for complex query write SQL by hand.