2

This is my problem: MySQL "Or" Condition

The solution is to group the OR statements, but i'm using CodeIgniters Active Record. Is there a way to group OR statements using Active Record? Or do I have to write the query myself?

I'm using $this->db->where() with $this->db->or_where()

It writes the query like this:

WHERE title = 'foobar' AND category = 2 OR category = 3

but what I need is:

WHERE title = 'foobar' AND (category = 2 OR category = 3)

I can't do this:

$this->db->where("title = 'foobar' AND (category = 2 OR category = 3)");

because i'm adding ORs using a foreach loop

Community
  • 1
  • 1
solstice
  • 442
  • 1
  • 4
  • 18

4 Answers4

2

You can do it manually as stated here:

Custom string: You can write your own clauses manually:

$where = "name='Joe' AND status='boss' OR status='active'";

$this->db->where($where);

As for your question:

$this->db->where("category = 1 AND (category = 2 OR category = 3)");

In 3.0-dev:

$this->db->select()
->group_start()
->or_like([ 'category' => 2, 'category' => 3 ])
->group_end()                
->where([ 'category' => 1 ]);

update

See the answers on this question if you're using CI 2.2. Choose an answer other than the accepted.

Or simply try this:

$categories = array(2, 3);

array_walk($categories, function(&$cat) { $cat = 'category = ' . $cat; });

$catstring  = implode(" OR ", $categories);
$where      = "category = 1 AND ($catstring)";
// => category = 1 AND (category = 2 OR category = 3)

$this->db->where($where);
Community
  • 1
  • 1
majidarif
  • 18,694
  • 16
  • 88
  • 133
  • I'm adding the ORs with a foreach loop so that won't work. The 3.0 dev part looks like exactly what I need but I'm using version 2.2 – solstice Oct 29 '14 at 07:36
  • The accepted answer for the link you posted looks like a good solution but why does it have 8 downvotes? – solstice Oct 29 '14 at 07:45
  • The last code you posted is exactly what that OP's own accepted answer is, to "Dynamically generate the SQL query and plug it into $this->db->where()". I don't think it's ugly it seems like a good answer. idk why it has 8 downvotes? – solstice Oct 29 '14 at 07:59
  • @sol , majidarif. there is a built in function in CI as i mentioned. – Karan Thakkar Oct 29 '14 at 08:00
  • @sol the downvotes seems to suggest that you loose the string validation but the documentation says: where() `All values passed to this function are escaped automatically, producing safer queries.` – majidarif Oct 29 '14 at 08:01
  • So it's a good way to do it right? But you wrote "Choose an answer other than the accepted" – solstice Oct 29 '14 at 08:06
  • @sol Based on the documentation it should be safe. So you can disregard my note on choosing another answer than the accepted. – majidarif Oct 29 '14 at 08:22
1

as OP mentioned you are generating OR using foreach (array) you can simply use

    $cat_array=array(2,3,4,5,6,7);
    $this->db->select('col1, col2')->from('table')->where("col1",1)->where_in('col2', $cat_array);

will generate

SELECT `col1`, `col2` FROM (`table`) WHERE `col1` = 1 AND `col2` IN (2, 3, 4, 5, 6, 7)
Karan Thakkar
  • 1,492
  • 2
  • 17
  • 24
0

you can't manipulate ground condition query using DB active record methods (where, or_where) , i would recommond to pass as sting in where() method

$this->db->where("category = 1 AND (category = 2 OR category = 3)");
Girish
  • 11,907
  • 3
  • 34
  • 51
0

In the Code Igniter Version 3.0-dev you can add groups in any query using group_start and group_end :

$this->db->select('col1, col2')
->where("category = 1")
->group_start()
->where("category = 2")
->or_where("category = 3")
->group_end();

Produces:

SELECT `col1`, `col2`
FROM `table_name`
WHERE category = 1 AND (category = 2 OR category = 3);
Ashish Awasthi
  • 1,484
  • 1
  • 19
  • 34