Currently I use CodeIgniter with regular queries, i.e.:
目前我使用CodeIgniter进行常规查询，即：
$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.
我已经开始研究ActiveRecord，它确实看起来不错，并且无论使用哪个数据库驱动程序都具有构建查询的优势 - 但是，我通常严格使用每个项目的单个数据库类型，所以这不是真正的对我有利。
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
谢谢
7 个解决方案
#1
8
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.
对我来说，我更喜欢运行常规查询，CI的活动记录消耗大量内存。因为它会将所有结果加载到内存中。如果你明白我的意思。至于复杂性，最好采用常规查询而不是CI的主动记录sytax。
#2
7
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.
我倾向于在大多数情况下更喜欢ActiveRecord。我发现它更具可读性，并且它使得构建动态查询变得更加容易，因为您不必将明显地连接原始SQL块。这意味着我可以向我的查询构建器添加各种条件，并且非常简单，并提供一些非常容易阅读的内容。
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.
有些东西，ActiveRecord的CodeIgniter实现非常不适合（并且让我想念Doctrine）并且我使用直接SQL，但它不会经常发生。
#3
5
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
对于简单的问题，你厌倦了编写SELECT blah blah，你可以使用活动记录稍微改变你的风格，因为在编写常规查询时，你很可能会产生syntex错误。活动记录是为此目的而设计的，除非您是专家，否则您不需要编写使用语法，其中出错的可能性很高。活动记录也提供逃避设施。您不熟悉它们（简单）可读的活动记录。看看这个例子
$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();
#4
5
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':
正如一些人所说，我也混合使用有效记录一些时间和纯直接sql用于非常复杂的查询，原因是当你需要一个接收大量os参数的方法并相应地更改查询时，使用它非常简单。例如，我有一个方法接收一个名为'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.
当然这只是一个简单的例子，但我已经构建了具有数百个行和条件的方法，可以改变通用的'get'方法，而活动记录使它非常好并且非常易读，因为你不需要在中间编写代码它正确地格式化查询。
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.
activerecord的另一个好处是它在语句中接受纯SQL，比如子查询和其他东西，所以你可以随意使用它。
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.
我说的是优点，但很明显，纯SQL总是会更快地执行，并且不会有调用函数的开销。但说实话，在大多数情况下，php解析器会这么快，它不会以富有表现力的方式影响最终结果，如果你必须制作大量的手动条件，你的代码可能和activerecord一样慢解析器无论如何。
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 ):
请注意，有时activerecord查询将无法按预期方式工作，因为它尝试以编程方式执行的逻辑方式构建查询，因此在使用“OR”语句时要小心，大多数时候你必须隔离它（和）：
$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.
如果你点不添加（），OR statament将影响整个where子句。所以，一旦你习惯了activerecord，它可以帮助很多，并且仍然可以进行快速和可读的查询。
#5
4
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)
（不是谈论联接等因为codeigniter支持它非常好并且可读）更像是数据透视查询，或者通过rownumber选择（如下所示）
$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();
#6
2
ActiveRecord doesn't always produce your queries in the order you want them, which can lead to unpredictable results. For example, this model:
ActiveRecord并不总是按您希望的顺序生成查询，这可能导致不可预测的结果。例如，这个模型：
$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.
但我希望在查询结束时检查showsearch，这就是为什么我把它放在首位。但ActiveRecord将它移动到查询的中间，我得到的结果不准确。
#7
2
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.
一个人为的例子可能是自动为具有creation_date字段的表的所有查询添加日期过滤器。
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.
CI还允许您将原始SQL与对象模型相当好地混合。基本上，您构造一个自定义查询，返回结果以水合对象。
#1
8
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.
对我来说，我更喜欢运行常规查询，CI的活动记录消耗大量内存。因为它会将所有结果加载到内存中。如果你明白我的意思。至于复杂性，最好采用常规查询而不是CI的主动记录sytax。
#2
7
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.
我倾向于在大多数情况下更喜欢ActiveRecord。我发现它更具可读性，并且它使得构建动态查询变得更加容易，因为您不必将明显地连接原始SQL块。这意味着我可以向我的查询构建器添加各种条件，并且非常简单，并提供一些非常容易阅读的内容。
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.
有些东西，ActiveRecord的CodeIgniter实现非常不适合（并且让我想念Doctrine）并且我使用直接SQL，但它不会经常发生。
#3
5
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
对于简单的问题，你厌倦了编写SELECT blah blah，你可以使用活动记录稍微改变你的风格，因为在编写常规查询时，你很可能会产生syntex错误。活动记录是为此目的而设计的，除非您是专家，否则您不需要编写使用语法，其中出错的可能性很高。活动记录也提供逃避设施。您不熟悉它们（简单）可读的活动记录。看看这个例子
$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();
#4
5
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':
正如一些人所说，我也混合使用有效记录一些时间和纯直接sql用于非常复杂的查询，原因是当你需要一个接收大量os参数的方法并相应地更改查询时，使用它非常简单。例如，我有一个方法接收一个名为'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.
当然这只是一个简单的例子，但我已经构建了具有数百个行和条件的方法，可以改变通用的'get'方法，而活动记录使它非常好并且非常易读，因为你不需要在中间编写代码它正确地格式化查询。
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.
activerecord的另一个好处是它在语句中接受纯SQL，比如子查询和其他东西，所以你可以随意使用它。
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.
我说的是优点，但很明显，纯SQL总是会更快地执行，并且不会有调用函数的开销。但说实话，在大多数情况下，php解析器会这么快，它不会以富有表现力的方式影响最终结果，如果你必须制作大量的手动条件，你的代码可能和activerecord一样慢解析器无论如何。
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 ):
请注意，有时activerecord查询将无法按预期方式工作，因为它尝试以编程方式执行的逻辑方式构建查询，因此在使用“OR”语句时要小心，大多数时候你必须隔离它（和）：
$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.
如果你点不添加（），OR statament将影响整个where子句。所以，一旦你习惯了activerecord，它可以帮助很多，并且仍然可以进行快速和可读的查询。
#5
4
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)
（不是谈论联接等因为codeigniter支持它非常好并且可读）更像是数据透视查询，或者通过rownumber选择（如下所示）
$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();
#6
2
ActiveRecord doesn't always produce your queries in the order you want them, which can lead to unpredictable results. For example, this model:
ActiveRecord并不总是按您希望的顺序生成查询，这可能导致不可预测的结果。例如，这个模型：
$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.
但我希望在查询结束时检查showsearch，这就是为什么我把它放在首位。但ActiveRecord将它移动到查询的中间，我得到的结果不准确。
#7
2
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.
一个人为的例子可能是自动为具有creation_date字段的表的所有查询添加日期过滤器。
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.
CI还允许您将原始SQL与对象模型相当好地混合。基本上，您构造一个自定义查询，返回结果以水合对象。