Active Records - Feature request: Brackets

[eluser]leber[/eluser]
Hi dudes

What i miss is a active record function to apply brackets around some where-statements.

Imagine:

Code:

$this->db->select('name');
$this->db->from('users');
$this->db->where('active','1');

so far, so good.

now maybe we wanne use a filter:

Code:

$this->db->where('status','1');

still ok...now we want active users with status 1 and status 2

Code:

$this->db->or_where('status','2');

result:

Code:

SELECT name FROM users WHERE active = '1' and status = '1' or status = '2'

that aint good, because we will also get inactive users now..something like

Code:

$this->db->select('name');
$this->db->from('users');
$this->db->where('active','1');
$this->db->and_bracked_where_with_a_less_stupid_name_start();
$this->db->where('status','1');
$this->db->or_where('status','2');
$this->db->and_bracked_where_with_a_less_stupid_name_stop();

would be nice...even if it, somehow, breaks the concept of AR...

Or am i just too stupid?

[eluser]Yash[/eluser]

Code:

$this->db->where(array('status'=>'1','status'=>'2'));

[eluser]xwero[/eluser]
it's a feature request that is popular If you find a good way to make it work share your code. If enough people try it and find no problems it will be added to the core if the Ellislab developers ok it.

But for your sql statement you could use the where_in method

Code:

$this->db->select('name');
$this->db->from('users');
$this->db->where('active','1');
$this->db->where_in('status',array(1,2));

This will give you
SELECT name FROM users WHERE active = 1 AND status IN (1,2)

[eluser]Zeeshan Rasool[/eluser]
Well , try it
$this->db->where('active','1');
$this->db->where(array('status'=>'1','status'=>'2'));
$this->db->select('name');
$this->db->from('users');

[eluser]xwero[/eluser]
Guys he needs
WHERE user = 1 AND (status = 1 OR status = 2)
The where you propose doesn't do that.

[eluser]leber[/eluser]
Almost forgot: Thx Xwero, the where_in idea was realy helpfull (in this case...brackets still would be very cool )

And thx xeeshan and yash for your advices, but as xwero allready pointed out: that was not what i needed

[eluser]m4rw3r[/eluser]
I'm working on an AR replacement (will be backwards compatible) which have support for what you need (+ subqueries :-) ).

It is only a prototype (currently only PHP5), so I have some work to do before I release it.

[eluser]Unknown[/eluser]
Yep the brackets feature would be great directly integrated

[eluser]jvicab[/eluser]
I created two functions using a code I found on the web to overcome this issue, one for like statements and another for where, but it should be avoid using something like $this->db_query("SELECT * FROM table WHERE user = 1 AND (status = 1 OR status = 2)";
$this->db->get();

Here is the function I added to CI_DB_active_record class on DB_active_rec.php on system/database/: (another similar for where)

function like_brackets()
{
// add a opening bracket
reset($this->ar_like);
$key = key($this->ar_like);
$this->ar_like[$key] = '('.$this->ar_like[$key];

// add a closing bracket
end($this->ar_like);
$key = key($this->ar_like);
$this->ar_like[$key] .= ')';

// update the AR cache clauses as well
if ($this->ar_caching === TRUE)
$this->ar_cache_like[$key] = $this->ar_like[$key];
return $this;
}

it can be used on a model like:
$this-db->from('mytable');
$this->db->where('id', '10');
$this->db->or_like('feature', 'oceanview');
$this->db->like('feature', 'waterfront');
$this->db->like_brackets();
$this->db->get();

This would produce a query like:

SELECT * FROM `mytable` WHERE `id` = '10'AND (`feature` LIKE '%oceanview%' OR `feature` LIKE '%waterfront%')