Codeigniter active record select, left join, count

Question

Hi There, I have a form that shows results from a database query, these results can have many other assets ajoined to them and I wanting to find a way of showing how many assets each elemement has. For example my table is of areas of england an other table has where the users live I current have this code,

$this->db->select('*');
$this->db->from('places');
$this->db->join('users, places.place_id = user.place_id, left');

$this->db->get();

The issue I am having is getting the query to return the place name and the number of users living in that place, it is possible?

Answer 1

select places.place_id,
       count(*) as UsersAtThisPlace
   from
       places,
       users
   where 
       places.place_id = users.place_id
   group by
       places.place_id

Don't know exactly how to implement via your PHP, but it should be as simple as the above query all within your

$this->db->select( "entire string example above" );

Additionally, if you had other descriptive elements from the places table, you could add those as well before the count (just for clarity), but would also have to include them in the group by... such as

select places.place_id,
       places.description,
       places.otherfield,
       count(*) as UsersAtThisPlace
   from
       places,
       users
   where 
       places.place_id = users.place_id
   group by
       places.place_id,
       places.description,
       places.otherfield