[fw-db] Zend_Db_Table along with a COUNT() expression on a child table.

Hi there,

 

I’m attempting to get a query working but have gotten a bit stuck.

 

The whole thing is involving 3 tables:

-          users_information

-          todo_comments

-          todo_entries

 

I have made a Zend_Db_Table which represents the todo_entries table. What I’m aiming at is a query which returns a rowset with all the entries from todo_entries with a specific id in one of it’s columns.

In addition to that, I’m attempting to do two things:

-          Each todo_entry row has a user_id to represent the creator of the entry. I want to join on some columns from the users_information table for the associated user.

-          Do a COUNT() on the todo_comments table. The table has a column named ‘entry_id’ to indicate which entry it’s related to. I want to do a COUNT() of the rows in todo_comments to read how many comments have been made to a specific todo_entry.

 

So far, I seem to have made the first part work. It’s the COUNT() I can’t really get to work and I’m hoping that some of your guys and girls may be of some assistance.

 

So far, the code I’m trying out with, looks like this:

 

$select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);

$select    ->setIntegrityCheck(false)

           ->where('category_id = ?', $categoryId)

           ->join('users_information', 'users_information.id = todo_entries.id', array('user_id' => 'id', 'name', 'username'))

           ->join('todo_comments', 'todo_comments.id = todo_entries.id', array('num_comments' => 'COUNT(*)'));

 

$result    = $this->fetchAll($select)->toArray();

Return $result;

 

 

Any help is much appreciated.

 

Kind regards

Christian Rasmussen