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

Might aswell try to keep this thread alive :)

I've worked a bit further on my problem and it seems that I have now come up with some clean SQL which does what I want - instead, I'm now having problem with getting it to work with Zend_Db_Select.
But ok, I wont keep you waiting. Here's the SQL. I have no clue to whether it's the right way or not, but it does seem to work:

SELECT `todo_entries`.*, `users_information`.`id` AS `user_id`, `users_information`.`name`, `users_information`.`username`,
(
SELECT COUNT(*)
FROM todo_comments c
WHERE c.entry_id = todo_entries.id
) AS num_comments
FROM `todo_entries`
INNER JOIN `users_information` ON users_information.id = todo_entries.user_id
WHERE (category_id = '1')
ORDER BY todo_entries.id

This seems to return just what I want but the nested SELECT is giving me a hard time.

I've tried various approaches, right now I'm going down this path:

$nested = $this->getAdapter()->select();
$nested ->from('todo_comments', new Zend_Db_Expr('COUNT(*)'))
->where('todo_comments.entry_id = todo_entries.id');

$select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select ->setIntegrityCheck(false)
->from(array('num_comments' => new Zend_Db_Expr('(' . (string) $nested . ')')))
->where('todo_entries.category_id = ?', $categoryId);

But the above approach results in this:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'todo_entries.id' in 'where clause'

So, if someone has an idea for converting my raw SQL to a select() then you're most welcome :)

Kind regards
Christian Rasmussen

-----Oprindelig meddelelse-----
Fra: Christian Aarø Rasmussen [mailto:Christian.Rasmussen@capana.com]
Sendt: 3. september 2009 13:21
Til: Andreas Kraftl; fw-db@lists.zend.com
Emne: SV: [fw-db] Zend_Db_Table along with a COUNT() expression on a child table.

I was playing around with this, have stumbled across something which might be called "progress".

My select object is now constructed as following:

$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.user_id', array('user_id' => 'id', 'name', 'username'))
->joinLeft('todo_comments', 'todo_entries.id = todo_comments.entry_id', array('num_comments' => new Zend_Db_Expr('COUNT(*)')));

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

As far as the COUNT() goes' this seems to work. I get the number of comments associated with a particular entry's ID. BUT! My result set only consists of one row. There are several rows in todo_entries which should be returned and my goal was to have a the number of comments associated with each entry as a part of each row in the result set. For a moment I also thought that the COUNT() returned the correct value, but where I'd like it to count only the rows where todo_entries.id are equal to todo_comments.entry_id and it simply seems to return me the complete number of rows in the todo_comments table - besides not returning me the complete result set from todo_entries.

Kind regards
Christian Rasmussen

-----Oprindelig meddelelse-----
Fra: Christian Aarø Rasmussen [mailto:Christian.Rasmussen@capana.com]
Sendt: 3. september 2009 07:20
Til: Andreas Kraftl; fw-db@lists.zend.com
Emne: SV: [fw-db] Zend_Db_Table along with a COUNT() expression on a child table.

Hi Andreas,

Thank you for your suggestion but it did not seem to work. It looks like the count was working ok but all the other values from the various colums I was also retreiving returned NULL.
You might be right about the Zend_Db_Expr and I've had a look at it on the Reference Guide but it leaves me a bit confused. As far as I can see from the examples, Zend_Db_Expr is being used through the from method.
I'm already working inside a Zend_Db_Table instance and so I'm a puzzled about how to make use of the examples from the Reference Guide. I've done done some various trial and error with the way it's being done in the examples but don't seem to have any luck.

I'm wondering if you, or anyone else reading this, could find the time to provide a more concrete example on how it should be done. Any help will be much appreciated.

Kind regards
Christian Rasmussen
________________________________________
Fra: Andreas Kraftl [andreas.kraftl@kraftl.at]
Sendt: 1. september 2009 15:55
Til: fw-db@lists.zend.com
Emne: Re: [fw-db] Zend_Db_Table along with a COUNT() expression on a child table.

Am Dienstag, den 01.09.2009, 15:02 +0200 schrieb Christian Aarø
Rasmussen:

> 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(*)'));

Try (COUNT(*)) instead of COUNT(*). Means, include the parenthesis.
If this doesn't work, try it with a Zend_Db_Expr.
http://framework.zend.com/manual/en/zend.db.select.html#zend.db.select.building.columns-expr

Greetings
Andreas
--
Kraftl EDV - Dienstleistungen
Linux, Linuxschulungen, Webprogrammierung
Autofabrikstraße 16/6
1230 Wien