Codeigniter Active Record - WHERE inside IFNULL and COUNT

Question

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

I want to show each locker and the sum of objects ordering by type (book, folder and box). Something like:

RACK_ID: 1
RACK_NUMBER: 001
LOCKER_NUMBER: 54
SUM_BOXES: 10
SUM_FOLDERS: 20
SUM_BOOKS: 10

My query:

$this->db->select('r.ID AS RACK_ID, r.NUMBER AS RACK_NUMBER, l.NUMBER AS LOCKER_NUMBER)
  ->select('IFNULL(COUNT(DISTINCT `box`.`ID`), 0) AS `SUM_BOXES`', FALSE)
  ->select('IFNULL(COUNT(DISTINCT `book`.`ID`), 0) AS `SUM_BOOKS`', FALSE)
  ->select('IFNULL(COUNT(DISTINCT `folder`.`ID`), 0) AS `SUM_FOLDERS`', FALSE);

$this->db->from('rack r');

$this->db->join('locker l', 'l.ID = r.LOCKER_fk', 'inner');
$this->db->join('box box', 'box.RACK_fk = r.ID', 'left');
$this->db->join('book book', 'book.RACK_fk = r.ID', 'left');
$this->db->join('folder folder', 'folder.RACK_fk = r.ID', 'left');

$this->db->where('r.LOCKER_fk', $this->session->userdata('LOCKER_ID'));

$this->db->group_by("r.ID");

This works fine, but the count returns all boxes, books and folders from each rack, ACESSIBLE OR NOT. 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?

Thanks.

EDIT: Ok so I found a solution: changing to this:

$this->db->join('box box', 'box.RACK_fk = r.ID AND box.ACESSIBLE= "1"', 'left');
$this->db->join('book book', 'book.RACK_fk = r.ID AND book.ACESSIBLE= "1"', 'left');
$this->db->join('folder folder', 'folder.RACK_fk = r.ID AND folder.ACESSIBLE= "1"', 'left');

score 0 · Answer 1 · answered May 09 '13 at 19:18

0

I am guessing something like this:

$this->db->where('r.LOCKER_fk', $this->session->userdata('LOCKER_ID'));
$this->db->where('box.ACESSIBLE', '1');

answered May 09 '13 at 19:18

Marc Audet

46,011
11
63
83

Thanks for the answer. I already tried but it doesn't work. It returns something different than expected result. – aseolin May 09 '13 at 19:44
Second idea, because you are grouping the records, instead of a where maybe you need a HAVING clause. Also, do you have other constraints like book.ACESSIBLE and folder.ACESSIBLE? – Marc Audet May 09 '13 at 19:48
Yes. I have box.ACESSIBLE, book.ACESSIBLE and folder.ACESSIBLE. Some records with value 0, others with value 1. I want to show only records whose value is 1. I tried with HAVING, no success. – aseolin May 09 '13 at 20:03
I assume that you hand coded the query at some point to develop/debug the SQL. You could always hand-code the query and use CI's query() function and not use active record. For complex queries, I avoid active records and go for native SQL. – Marc Audet May 09 '13 at 20:11
I have tried to write this query in mysql (whitout active record), but I can not. My knowledge have not reached this level. – aseolin May 09 '13 at 20:16

Codeigniter Active Record - WHERE inside IFNULL and COUNT

1 Answers1