1

I a developing in zend and have a rather large mysql query. The query works fine and i get the list I expect. I am doing this using Select->Where.... below is the query.

SELECT DISTINCT `d`.* FROM `deliverable` AS `d` INNER JOIN `groups` AS `g1` ON d.id = g1.deliverable_id INNER JOIN `groupmembers` AS `gm1` ON g1.id = gm1.group_id LEFT JOIN `connection` AS `c` ON d.id = c.downstreamnode_id LEFT JOIN `deliverable` AS `d1` ON c.upstreamnode_id = d1.id INNER JOIN `deliverable` AS `d2` ON CASE WHEN d1.id IS NULL THEN d.id ELSE d1.id END = d2.id INNER JOIN `groups` AS `g` ON d2.id = g.deliverable_id INNER JOIN `groupmembers` AS `gm` ON g.id = gm.group_id WHERE (g1.group_type = 100) AND (gm1.member_id = 1) AND (c.downstreamnode_id IS NULL OR d.restrict_access = 1) AND (g.group_type = 100 OR g.group_type = 110) AND (gm.member_id = 1) AND (d.deliverable_type = 110 OR d.deliverable_type = 100) GROUP BY CASE WHEN c.downstreamnode_id IS NULL THEN d.id ELSE c.downstreamnode_id END

Only problem is when I try to count the rows in a mysql query I only get 1 returned. below is the query

SELECT DISTINCT count(*) AS `rowCount` FROM `deliverable` AS `d` INNER JOIN `groups` AS `g1` ON d.id = g1.deliverable_id INNER JOIN `groupmembers` AS `gm1` ON g1.id = gm1.group_id LEFT JOIN `connection` AS `c` ON d.id = c.downstreamnode_id LEFT JOIN `deliverable` AS `d1` ON c.upstreamnode_id = d1.id INNER JOIN `deliverable` AS `d2` ON CASE WHEN d1.id IS NULL THEN d.id ELSE d1.id END = d2.id INNER JOIN `groups` AS `g` ON d2.id = g.deliverable_id INNER JOIN `groupmembers` AS `gm` ON g.id = gm.group_id WHERE (g1.group_type = 100) AND (gm1.member_id = 1) AND (c.downstreamnode_id IS NULL OR d.restrict_access = 1) AND (g.group_type = 100 OR g.group_type = 110) AND (gm.member_id = 1) AND (d.deliverable_type = 110 OR d.deliverable_type = 100) GROUP BY CASE WHEN c.downstreamnode_id IS NULL THEN d.id ELSE c.downstreamnode_id END

i generate this from by using the same 'select' that generated the first query but I reset the columns and add count in.

$this->getAdapter()->setFetchMode(Zend_Db::FETCH_ASSOC);
    $select
        ->reset( Zend_Db_Select::COLUMNS)
        ->columns(array('count('.$column.') as rowCount'));
    $rowCount = $this->getAdapter()->fetchOne($select);

This method works fine for all my other queries only this one i am having trouble with. I suspect it has something to do the 'CASE' I have in there but it is strange because I am getting the correct rows the the first query. Any ideas. Thanks.

FYI below are two queries that I have working successfully.

SELECT DISTINCT `po`.* FROM `post` AS `po` INNER JOIN `postinfo` AS `p` ON po.postinfo_id = p.id WHERE (p.creator_id = 1) ORDER BY `p`.`date_created` DESC

SELECT DISTINCT count(*) AS `rowCount` FROM `post` AS `po` INNER JOIN `postinfo` AS `p` ON po.postinfo_id = p.id WHERE (p.creator_id = 1) ORDER BY `p`.`date_created` DESC

In this one I have 4 rows returned in the first query and 'int 4' returned for the second one. Does anyone know why it doesnt work for the big query?

viciouskinid
  • 59
  • 1
  • 8

3 Answers3

0

Move your DISTINCT.

SELECT COUNT(DISTINCT `po`.*) AS `rowCount` ...
Interrobang
  • 16,984
  • 3
  • 55
  • 63
  • Thanks but it didnt work. Just to confirm the below code works as intended. SELECT DISTINCT count(*) AS `rowCount` FROM `post` AS `po` even with the Distinct where it is, it is the other query i posted that i am having trouble with. I even tried getting rid of the distinct all together but not luck still returning 1. Any other ideas? Thanks – viciouskinid Mar 15 '12 at 22:18
  • The second of your queries that you mention is working likely does not work how you expect. `SELECT DISTINCT count(*)` will count all rows, not just unique ones, and then return 1 row as a result. Then `DISTINCT` will filter that down to only the unique rows, but since only one row is returned (the count), it doesn't help to include `DISTINCT`. If you want to count distinct rows you must place the `DISTINCT` inside the `COUNT`. – Interrobang Mar 15 '12 at 22:33
0

Ok figured it out It was the GROUP BY that was causing only 1 result to be returned. Thanks Interrobang for you help I am sure that using DISTINCT incorrectly will have caused me a headache in the future.

viciouskinid
  • 59
  • 1
  • 8
0

Try using SQL_CALC_FOUND_ROWS in your query? http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Using SQL_CALC_FOUND_ROWS is mysql-specific, but it's pretty nice for getting a full record count even when your initial query contains a limit. Once you get the count, don't include SQL_CALC_FOUND_ROWS in subsequent queries for extra records since that will cause extra load on your query.

Your initial query would be:

SELECT SQL_CALC_FOUND_ROWS  DISTINCT `d`.* FROM `deliverable` AS `d` INNER JOIN `groups` ...

You'll have to do a subsequent call after your initial query executes to get the count by doing a SELECT FOUND_ROWS().

If you do a little searching, you'll find someone who extended Zend_Db_Select to include this ability.

Gordon Forsythe
  • 356
  • 3
  • 7