CI_Mixing_Active_Record_&_Classic_Styles

CI_Mixing_Active_Record_&_Classic_Styles

Mixing Active Record and 'Classic' Styles
CI doesn't insist that you use Active Record. You can also use CI to issue straight SQL queries. For instance, assuming you loaded the database in your constructor, you can still write queries like this:
$this->db->query("SELECT id, name, url FROM sites WHERE 'type' = 'dynamic'");
Personally, I find Active Record easier to use. Conceptually, setting out my query in an array makes it easier to see and manipulate as an entity than writing it in SQL syntax. It's slightly more verbose, but clearly structured; it automatically escapes data; and it may be more portable. It also minimizes typing errors with commas
and quotes.
There are a few cases, however, where you may have to resort to the original SQL. You might want to do complex joins, or another example is if you need to use multiple 'where' conditions. If you want to find the websites associated with client 3, but only those of two specific types, you may need to put brackets around the SQL to make sure the query is correctly interpreted.
In cases like these, you can always write out the SQL as a string, put it in a variable, and use the variable in CI's $this->db->where() function, as follows:
$condition = "client ='3' AND (type ='dynamic' OR type='static')";
$this->db->where($condition);.
Without the brackets this is ambiguous. Do you mean:
(client='3' AND type = 'dynamic') OR type = 'static'
or
client='3' AND (type = 'dynamic' OR type = 'static')

Well, yes of course, it's obvious, but the machine usually guesses wrong. Incidentally, be careful with the syntax of $condition. The actual SQL query is:
client='3' AND (type = 'dynamic' OR type = 'static')
The double quotes come from the variable assignment:
$condition = " ";
It's easy to get your single and double quotes confused.
Some of the CI expressions I've quoted above, like $this->db->affected_rows(), are not a part of its Active Record model. But they can be mixed in easily.
The only time you might run into problems is if you try to mix Active Record and straight SQL in the same query. (I haven't tried this. If you have a lot of time on your hands, you could test it out, but frankly, I think that would indicate a sad lifestyle. Try train-spotting instead. At least it gets you out into the fresh air! I use CI because I'm too busy not to!)