CodeIgniter Active Record vs. regular queries

Question

Currently I use CodeIgniter with regular queries, i.e.:

$sql = "
    SELECT *
    FROM my_table
    WHERE item_id > 1
";    
$q = $this->db->query($sql);

I have started looking into ActiveRecord, and it does look nice, and has the advantage of the query being built regardless of which database driver is used - however, I strictly use a single database type per project in general, so this is not really a benefit for me.

I found that regular queries (as I have in my example) are more readable and easier maintained in my opinion, so I am currently thinking of keeping with regular queries.

Besides for the reasons mentioned above, which should I choose, and for what reason(s)?

Thank you

Answer 1

Almost two years later I found this question. Some of my colleages have already answered with several advantages or disadvantages, I just want to add an opinion by my personal experience:

As some said, I too mix the using active record some times and pure straight sql for very complex queries, the reason is that it's very simple to use it when you need a method that receives lots os parameters and changes the query accordingly. For instance, I have a method that receives an array of parameters called 'options':

if(!empty($options['login']))
{
    $this->db->where('tl.login', $options['login']);
}
if(!empty($options['ip']))
{
    $this->db->where('tl.ip', $options['ip']);
}
if(!empty($options['sucesso']))
{
    $this->db->where('tl.sucesso', $options['sucesso']);
}

if(isset($options['usuarios_existentes']) && $options['usuarios_existentes'])
{
    $this->db->join('usuario u', 'tl.login = u.login');
}
else
{
    $this->db->join('usuario u', 'tl.login = u.login', 'LEFT');
}

if(!empty($options['limit']))
{
    $this->db->limit($options['limit']);
}
else
{
    $this->db->limit(50);
}

return $this->db->select('tl.id_tentativa_login, tl.login, DATE_FORMAT(tl.data, "%d/%m/%Y %H:%i:%s") as data, tl.ip, tl.sucesso', FALSE)
                ->from('logs.tentativa_login tl')
                ->order_by('tl.data', 'DESC')
                ->get()->result();

Of course it's just a simple example, but I have built methods with hundreds of lines and conditions that could change a generic 'get' method, and active record makes it really nice and very readable because you do not need to write codes in the middle of it to format the query properly.

You can even have joins and other stuff that can be conditional., so you can use a generic centralized method, that way avoiding rewriting most of the code and duplicating parts of it (terrible for maintance), it's not only readable, but it keeps your queries fast because only loads what you need:

if(!empty($opcoes['com_maquina']))
{
    if(strtoupper($opcoes['com_maquina'])=='SIM')
    {
        $this->db->join('maquina m', 'm.id_local = l.id_local');
    }
    elseif(strtoupper($opcoes['com_maquina'])=='NAO')
    {
        $this->db->join('maquina m', 'm.id_local = l.id_local', 'LEFT');
        $this->db->where('m.id_maquina IS NULL');
    }
}

Another good point for activerecord, is that it accepts pure SQL in the statements, like subqueries and other stuff, so you can use it as you please.

I'm talking about the advantages, however, it's obvious that pure SQL will always be executed faster and will not have the overhead of calling functions. but to tell the truth, in most cases php parser will do it so fast that it won't affect the final result in an expressive way, and if you have to make a lots of manual conditions, your code maybe as slow as the activerecord parser anyway.

Just be aware that sometimes activerecord queries won't work the way you expect, because it tries to build the query in a logical way it was programmed to do, so be careful when using 'OR' statements for instance, most times you have to isolate it ( and ):

$this->db->where('(m.ultimo_status < DATE_ADD(NOW(), INTERVAL -2 HOUR) OR m.ultimo_status IS NULL)');

If you dot not add the ( ), the OR statament will affect the whole where clause. So, once you get used to activerecord, it can help alot and still make fast and readable queries.

Answer 2

Well for simple quesries where you are tireds of writing SELECT blah blah you can change your style a bit using active record because while writing a regular query there is much chance that you might make a syntex error. Active record was designed for this purpose that you dont need to write usuall syntax where chance of making mistake is high unless you are an expert. Also active record gives escaping facility. You are not familiar with active record they are (when simple) readable too. Take a look at this example

$array  =   array(
        'user.username',
        'user.email',
        'user.password',
        'other.blah',
        'other.blah'
);
$where  =   array(
        'active'    =>  1,
        'other'     =>  'blahblah'
);  

return $this->db
        ->select($array)
        ->from('user')
        ->join('other','other.user_id = user.id','left')
        ->where($where)
        ->get()
        ->result();    

Answer 3

There's a huge benefit to using an object model for the queries. While you can perform string parsing and concatenation to build up a query over several functions, this is time consuming and extremely error prone. With an object model, you can pass along a reference and continue building the query or pass it to pre-processors before execution.

A contrived example might be to automatically add a date filter to all queries for tables with a creation_date field.

CI also lets you mix raw SQL with the object model fairly well. Basically, you construct a custom query that returns results to hydrate objects.

Answer 4

ActiveRecord doesn't always produce your queries in the order you want them, which can lead to unpredictable results. For example, this model:

        $this->db->select('page, content');
        $this->db->from('table');
        $array = array('title' => $searchq, 'content' => $searchq);
        $this->db->or_like($array, 'both'); 
        $this->db->where('showsearch', 'Yes'); 
        return $this->db->count_all_results();

produces this query:

        SELECT COUNT(*) AS `numrows`
        FROM (`table`)
        WHERE `showsearch` =  'Yes'
        AND  `title`  LIKE '%term%'
        OR  `content`  LIKE '%term%'

But I want the check for showsearch to be done at the end of the query, which is why I put it there in the first place. But ActiveRecord moves it to the middle of the query and I get results that aren't accurate.

Answer 5

I tend to prefer ActiveRecord for the most part. I find it more readable and that it makes building dynamic queries much much easier as you don't have to monkey around with explicitly concatenating chunks of raw SQL together. This means I can add all sorts of conditionals to my query builders with very little fuss and come out with something that's very easy to read.

There are some things for which the CodeIgniter implementation of ActiveRecord is very poorly-suited (and makes me miss Doctrine) and I use straight SQL for that but it doesn't happen too often.

Answer 6

well for me i prefer running regular queries, CI's active record consumes to much memory. because it will load all result in the memory. If you know what i mean. As for complexity it's better to go with regular query rather sticking to CI's active record sytax.

Answer 7

Well, my main reason is that it works fast and secure. Since it automatically escapes values etc. But when it comes to complex query's I suggest using a normal querystring.

(not talking about joins etc. Since codeigniter supports it pretty well and readable) More like pivot query's, or selection by rownumber (like the below)

$query = $this->db->query('SELECT * FROM
  (SELECT  @row := @row + 1 as row, t.*
   FROM `default_red_albums` t, (SELECT @row := 0) r) AS view
WHERE `row` IN(' . $in . ')');

return $query->result();