Active Record help

[eluser]callumd[/eluser]
Hi There,

Having trouble replicating a query using Active Record.

Here's the SQL (this works in MySQL):

Code:

SELECT project_name, task_name, urgency
FROM projects, tasks, urgency
WHERE tasks.project_id = projects.project_id AND tasks.urgency_id = urgency.urgency_id;

This doesn't work:

Code:

$this->db->select('project_name, task_name, urgency');
$this->db->where('tasks.project_id', 'projects.project_id');
$this->db->where('tasks.urgency_id', 'urgency.urgency_id');
$this->db->from('tasks, projects, urgency');
$query = $this->db->get();

The SQL it produces is close, but still malformed, hence not working:

Code:

SELECT `project_name`, `task_name`, `urgency` FROM (`tasks`, `projects`, `urgency`) WHERE `tasks`.`project_id` = 'projects.project_id' AND `tasks`.`urgency_id` = 'urgency.urgency_id'

Help! Thanks.

[eluser]tonanbarbarian[/eluser]
write the query as a propery join query and it will work fine

Code:

SELECT project_name, task_name, urgency
FROM projects
INNER JOIN tasks ON tasks.project_id = projects.project_id
INNER JOIN urgency ON tasks.urgency_id = urgency.urgency_id;

Code:

$this->db->select('project_name, task_name, urgency');
$this->db->join('tasks', 'tasks.project_id=projects.project_id', 'inner');
$this->db->join('urgency', 'tasks.urgency_id=urgency.urgency_id', 'inner');
$this->db->from('projects');
$query = $this->db->get();

[eluser]callumd[/eluser]
Thanks tonanbarbarian,

This all seems much slower to me than just writing the SQL.. is the "industry standard" that no one writes SQL anymore? Us web developers should all be using these abstraction classes?

[eluser]tonanbarbarian[/eluser]
there is nothing saying you HAVE to use active record to write the query
In fact there are lots of times when i do not use active record for my queries

you could just as easily done

Code:

$query = $this->db->query('SELECT project_name, task_name, urgency
FROM projects
INNER JOIN tasks ON tasks.project_id = projects.project_id
INNER JOIN urgency ON tasks.urgency_id = urgency.urgency_id');

the one advantage to using active record is that it can do minimal transformation on the sql for different database types, such as how the tables and field names are escaped
this allows your code to be easily ported to another database

[eluser]callumd[/eluser]
Thanks again.