"Where" condition

[eluser]wise_fox[/eluser]
Hi to all!
I adore CI, but yesterday I found some issue that I can't manage.
I have code like that:

Code:

$this -> db -> not_like('body','-');
...
$this -> db -> where_in('zone',$tld);
...
$this -> db -> or_not_like('body',"0");
$this -> db -> or_not_like('body',"1");
...
$this -> db -> or_not_like('body',"9");
...
$this -> db -> where('length <'.$StartCount);
$this -> db -> or_where('length >'.$EndCount);
...
$this -> db -> not_like('body',$Keyword,'after');
...
$this -> db -> not_like('body',$Keyword,'before');
...

The question is I would like to have the query like "SELECT * FROM table WHERE (body NOT LIKE <text>) AND (body NOT LIKE "0" OR body NOT LIKE "1" OR body NOT LIKE "2")"
But CI doesn't add any brackets in query and it goes as follow: "SELECT * FROM table WHERE body NOT LIKE <text> AND body NOT LIKE "0" OR body NOT LIKE "1" OR body NOT LIKE "2""
The last query differs from the first one.
Could somebody help me with it?

[eluser]jmadsen[/eluser]
There are no parenthesis in Active Record - you will need to write the query yourself.

This is because...

[climbs_up_on_high_horse]
Most web developers seem to feel that knowing even the most rudimentary sql is something that doesn't involve them personally, or that they are scared to death of, and so they will write a whole framework of "Concatentate SQL Strings via Functions" (aka, Active record, etc) rather than work through a basic Sql 101 course. Then they'll realize it failed miserably because it never occurred to them to include such basics as parenthesis, unions, etc.
[climbs_down_off_high_horse]

All that most of Active Record does is a bunch of string manipulation and concatenation. If you've memorized all the Active Record functions, you've probably memorized what you need to write a decent query. I personally still have never seen an example of creating "reusable query parts" that would take advantage of the objects involved, and my own attempt made me feel it wasn't really worth it.

So, if your query is very simple and you want to use it, go ahead. But if you can already write the whole thing (like you did above), just do:

Code:

$sql = 'SELECT blah, blah, ? ';
$result = $this->db->query($sql, array(MY_VAR));
return result;

And make life much easier on yourself. (That said, the Update and Insert function ARE very time saving, and I'd encourage their use )

[eluser]wise_fox[/eluser]
ok. I guess that I shouldn't use Active Record. Thanks a lot for your post

[eluser]CroNiX[/eluser]
Active record is great for most "common" type queries....but when it gets to the more complex like you are trying to do, its just easier to write the entire query as a single string and just $this->db->query($query).

[eluser]wise_fox[/eluser]
Hey guys! I managed the problem! Take a look at:

Code:

$this -> db -> where('(length <'.$StartCount,NULL,FALSE);
$this -> db -> or_where('length >'.$EndCount.')',NULL,FALSE);

Do you see what do I mean?

[eluser]techgnome[/eluser]
I was about say, I stuff complex wheres (with parens and everything) into my ->where() all the time... works for me.

-tg

addendum - while I'll write my queries in workbench... I convert them to CI AQ...
Take this example I posted here:
http://ellislab.com/forums/viewthread/168756/

The very next day after posting that, I expanded the were to act differently based on some parameters passed in... for browsing, I wanted items that START with the specified letter... that changes the LIKE clause (to LIKE 'A%' )... for searches, I wanted items that CONTAIN the searched criteria... changes the LIKE again (to LIKE '%A%' ) ... with a full on string query, I'd have to have either two methods... and two queries... in this case I opted for the saner and simpler one method, dual purpose option. haven't had any issues with it (yet :/ )

-tg

[eluser]jmadsen[/eluser]
yep, you can creatively "stuff" all kind of ...er, stuff (try a nested table in the "from" method, for fun)

But it really goes against the point of ActiveRecord, I think. On top of it all, look at wise_fox's first post...look how many lines of code he has to write to make a "simple" query. Plus, needing to run profiler or last_query() to do any simple debugging, as opposed to just being able to look at it.

I guess it's nice to have options, but i don't see ActiveRecord as being a very good option for most real-world queries.