[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.
