Why is Active Record good for database queries?

[eluser]Lpeek[/eluser]
Hey! I'm wondering if active record does anything behind-the-scenes which makes it better than simply writing a query? As a lot of people seem to complain when others post code that doesnt use AR, simply saying 'why do people write queries when CI lets you use active-record?)

I'd love to know why the following is supposed to be better than the manual method which is shorter and easier to read?

Code:

$this->db->select('uid');
$this->db->from('users');
$this->db->where('activationkey',$key);
$query = $this->db->get();

vs

Code:

$sql = "SELECT uid FROM users WHERE activationkey = ?";
$query = $this->db->query($sql, array($key));

[eluser]boltsabre[/eluser]
Okay, active queries automatically escape the "where" clauses and what have you, but in your above example you're doing that as well.

The other added advantage is that active records are a layer between you and your database. I've forgotten the term off the top if my head, but it means if you switch to another type of DB from my_sql your, active records will still work, but you will have to re-write native my_sql to the new DB syntax.

They may be more to it than just this, but I'm not aware of anything else...

[eluser]PhilTem[/eluser]
1) CI's AR class escapes the queries automatically with proper DB syntax
2) It's universal since it's an "abstract" layer for communicating with your database. AR allows you to have no knowledge of what DB system is running (postgresql, mysql, mssql, ...) you only write the queries the way you want them
3) (This may not be an official advantage, but I personally enjoy reading AR class calls and it makes reading the queries easier since you don't have to run through a line of code looking for keywords like "SELECT", "FROM" in order to get the meaning of the query. Plus altering a query is easier and you most likely don't produce syntax errors when adding a "JOIN LEFT" via "db->join('table', 'left');"

[eluser]Lpeek[/eluser]
Ah ok, that reason makes sense, so its more like, write a bit more code for better portability in the future?

[eluser]PhilTem[/eluser]
It really depends on whether you know that your code will only run on one configuration or if you want to have it portable.
If you create a project for a client and they are running on MySQL and will always be, there is no real necessity to use AR (except that I think it's easier reading). However, if you're creating a CMS and you don't know if your clients will be running on MySQL, MSSQL, PostgreSQL, you want to use AR.

Or, imagine you have to develop a project running with a PostgreSQL DB but you don't have a f****** clue, how PostgreSQL queries are written: Use the AR and you don't need to know

[eluser]SPeed_FANat1c[/eluser]

(except that I think it’s easier reading).

Depends on what you are used to. I used to pure SQL, and its not difficult to read at all.

But again - if you write in AR then you stil have to kwow both ways because CI has AR, but as far as I know those programs like DreamCoder, SQLYog, PHPadmin and so on - they do not have AR so you still need to be used to natural SQL. So then if you are not planinng to change database then I agree with those who say that AR is not better than natural SQL.

So what is easier to learn - two ways to do same thing or one way?

[eluser]PhilTem[/eluser]
Go for what is easier/nice/more convenient to you.
I personally think it's easier to learn AR since (a) almost all AR classes are similar to CI's - so learning this AR library won't be no harm -, (b) you really don't need to think about which database driver is being used, your code will work for all of them, and © I just like using AR (mostly because I don't have to consider escaping my queries, which can be a hassle sometimes )

[eluser]SPeed_FANat1c[/eluser]

mostly because I don’t have to consider escaping my queries, which can be a hassle sometimes

Code:

$sql = "SELECT uid FROM users WHERE activationkey = ?";
$query = $this->db->query($sql, array($key));

this way they are escaped and still using standard sql as someone already said.