title: Codeigniter Active Record - WHERE inside IFNULL and COUNT
body: <p>I have an application that basically simulates a locker. Inside each locker I have N racks. Inside each rack I have N boxes, N folders and N books. Some of this objects are not acessible (they have an boolean attribute called ACESSIBLE).</p>
  
  <p>I want to show each locker and the sum of objects ordering by type (book, folder and box). Something like:</p>
  
  <p>RACK_ID: 1<br />
  RACK_NUMBER: 001<br />
  LOCKER_NUMBER: 54<br />
  SUM_BOXES: 10<br />
  SUM_FOLDERS: 20<br />
  SUM_BOOKS: 10<br /></p>
  
  <p>My query:</p>
  
  <blockquote>
  <pre><code>$this-&gt;db-&gt;select('r.ID AS RACK_ID, r.NUMBER AS RACK_NUMBER, l.NUMBER AS LOCKER_NUMBER)
    -&gt;select('IFNULL(COUNT(DISTINCT `box`.`ID`), 0) AS `SUM_BOXES`', FALSE)
    -&gt;select('IFNULL(COUNT(DISTINCT `book`.`ID`), 0) AS `SUM_BOOKS`', FALSE)
    -&gt;select('IFNULL(COUNT(DISTINCT `folder`.`ID`), 0) AS `SUM_FOLDERS`', FALSE);
  
  $this-&gt;db-&gt;from('rack r');
  
  $this-&gt;db-&gt;join('locker l', 'l.ID = r.LOCKER_fk', 'inner');
  $this-&gt;db-&gt;join('box box', 'box.RACK_fk = r.ID', 'left');
  $this-&gt;db-&gt;join('book book', 'book.RACK_fk = r.ID', 'left');
  $this-&gt;db-&gt;join('folder folder', 'folder.RACK_fk = r.ID', 'left');
  
  $this-&gt;db-&gt;where('r.LOCKER_fk', $this-&gt;session-&gt;userdata('LOCKER_ID'));
  
  $this-&gt;db-&gt;group_by("r.ID");
  </code></pre>
  </blockquote>
  
  <p>This works fine, but the count returns all boxes, books and folders from each rack, <strong>ACESSIBLE OR NOT</strong>. I want to input a WHERE condition on each count that returns only acessible objetcs (something like WHERE boxes.ACESSIBLE = 1). How can I do that?</p>
  
  <p>Thanks.</p>
  
  <p>EDIT: Ok so I found a solution: changing to this:</p>
  
  <pre><code>$this-&gt;db-&gt;join('box box', 'box.RACK_fk = r.ID AND box.ACESSIBLE= "1"', 'left');
  $this-&gt;db-&gt;join('book book', 'book.RACK_fk = r.ID AND book.ACESSIBLE= "1"', 'left');
  $this-&gt;db-&gt;join('folder folder', 'folder.RACK_fk = r.ID AND folder.ACESSIBLE= "1"', 'left');
  </code></pre>