How to write a query in CodeIgniter Active Record

Question

Hi all, I'm having some issues getting codeigniter active record to produce a query like this:

SELECT fruits.* FROM fruits
WHERE fruits.color = 'red'
AND ( fruits.size = 'medium' OR fruits.name = 'kiwi' OR fruits.length = 7 );

Basically I want to have several or clauses but one where clause that is always enforced.

$this->db->select( 'fruits.*' );
$this->db->from( 'fruits' );
$this->db->where( 'fruits.color', 'red' );
$this->db->or_where( 'fruits.size', 'medium' );
$this->db->or_where( 'fruits.name', 'kiwi' );
$this->db->or_where( 'fruits.length', 7 );

Produces something like:

SELECT fruits.* FROM fruits WHERE fruits.color = 'red' OR fruits.size = 'medium' OR fruits.name = 'kiwi' OR fruits.length = 7;

I need to enforce that color is always red.

Is there a decent way to do this?

Answer 1

There is no way to achieve this properly using CodeIgniter Active Record.

The only way that's not too ugly is using a custom string with the where() function like this:

$this->db->select( 'fruits.*' );
$this->db->from( 'fruits' );
$where = "fruits.color = 'red' AND ( fruits.size = 'medium' OR fruits.name = 'kiwi' OR fruits.length = 7 );";
$this->db->where( $where );

IMPORTANT: note that using a custom string, your variable WILL NOT be escaped, thus, you need to use $this->db->escape()

$this->db->select( 'fruits.*' );
$this->db->from( 'fruits' );

$color = 'red';
$size = 'medium';
$name = 'kiwi';
$length = 7;
$where = "fruits.color = '".$this->db->escape($color)."' AND ( fruits.size = '".$this->db->escape($size)."' OR fruits.name = '".$this->db->escape($name)."' OR fruits.length = '".$this->db->escape($length)."');";
$this->db->where( $where );

Edit:

I got confused on the good query, I corrected it :)