Active Record Query

[eluser]jamesf[/eluser]
Hopefully a quick question, I'm having a problem with a JOIN query.

I've got a PROJECTS table which can have multiple ELEMENTS, I've implemented a soft delete approach with a field that is either 'ACTIVE' or 'INACTIVE'. I'm trying to find all the active projects with the count of the active elements

Code:

$this->db->select('projects.id, projects.name, COUNT(elements.element_type_id) AS num_elements');
$this->db->from('projects');
$this->db->join('elements', 'elements.project_id = projects.id', 'left');
$this->db->where("projects.status='ACTIVE'");
$this->db->where("elements.status='ACTIVE'");
$this->db->group_by('projects.id');

If a project has no elements that are active then the project is not returned because of the:

Code:

$this->db->where("elements.status='ACTIVE'");

Any ideas would be extremely helpful.

James

[eluser]Symcrapico[/eluser]
I think that you have to group by "element.project_id" rather than projects.id.

If not, post your table schemas so we understand better what your trying to achieve.

[eluser]jamesf[/eluser]
It was just a problem with the WHERE clause on the joined table. Had to hack it a little bit as Active Record doesn't really do sub queries but found a way around this on another thread using:

Code:

$this->db->ar_from[] = "projects LEFT JOIN (SELECT ... WHERE )";

[eluser]Symcrapico[/eluser]
Could you post your table schemas with some sample data and the desired results. Unless Im not understanding what you are trying to do, Im pretty sure you dont have to hack AR to get it working.

[eluser]jamesf[/eluser]
Basically in my database a project can have many elements and in my SQL i wanted to find the number of elements for each project that are 'Active'.

Code:

projects
    - id
    - name
    - status [enum(ACTIVE,INACTIVE)]

elements
    - id
    - project_id
    - name
    - status

So I needed to implement this SQL in active record which I was under the impression wasn't possible.

Code:

SELECT projects.*, num_elements
FROM projects
LEFT JOIN
(
    SELECT project_id, COUNT(*) as num_elements
    FROM elements
    WHERE status='Active'
    GROUP BY project_id
) as elements ON elements.project_id = projects.id
WHERE projects.status = 'ACTIVE'
GROUP BY projects.id

I thought you could apply a general where clause on a join table e.g.

Code:

$this->db->where("elements.status='ACTIVE'");

but if I add this only projects that have active elements are returned which wouldn't be correct. Not to worry too much about it, I've managed to find a solution but thanks for your help.