[fw-db] Queries with LEFT JOIN &COUNT(table.column) possible with Zend_Db_Query?

Hi,
I've got the following data structure, and am trying to build a query to
list all the rows in table1 with a corresponding count of the rows in
table2. (I'm using Zend_Db_Select because in my real application I need to
build this query dynamically). I'm having trouble building the query I want
with Zend_Db_Select - can anyone tell me if it is in fact possible?

mysql> select * from table1;
+----------+---------------+
| table1ID | someCol1 |
+----------+---------------+
| 1 | table 1 row 1 |
| 2 | table 1 row 2 |
| 3 | table 1 row 3 |
+----------+---------------+

mysql> select * from table2;
+----------+---------------+----------+
| table2ID | someCol2 | table1ID |
+----------+---------------+----------+
| 1 | table 2 row 1 | 1 |
| 2 | table 2 row 2 | 1 |
| 3 | table 2 row 3 | 1 |
| 4 | table 2 row 4 | 3 |
+----------+---------------+----------+

The query I want is
SELECT table1.table1ID,table1.someCol1,COUNT(table2ID) as someCount
FROM table1
LEFT JOIN table2 USING (table1ID)
GROUP BY table1.table1ID

which produces
+----------+---------------+-----------+
| table1ID | someCol1 | someCount |
+----------+---------------+-----------+
| 1 | table 1 row 1 | 3 |
| 2 | table 1 row 2 | 0 |
| 3 | table 1 row 3 | 1 |
+----------+---------------+-----------+

For various reasons, I'd like to build the query in two parts:

$db = $this->getDefaultAdapter();
$select = $db->select();

$select->from('table1',array('table1ID','someCol1'));

if($doCount)
{
$select->joinLeftUsing('table2','table1ID')
->from('table1',array('someCount' => 'COUNT(table2ID)'))
->group('table1.table1ID');
}

echo $select->__toString();

However, this adds a spurious INNER JOIN and extra columns and thus gives
the wrong result:
SELECT `table1`.`table1ID`, `table1`.`someCol1`, `table2`.*, COUNT(table2ID)
AS `someCount`
FROM `table1`
LEFT JOIN `table2` ON `table2`.table1ID = `table1`.table1ID
INNER JOIN `table1` AS `table1_2`
GROUP BY `table1`.`table1ID`

+----------+---------------+----------+---------------+----------+-----------+
| table1ID | someCol1 | table2ID | someCol2 | table1ID | someCount
|
+----------+---------------+----------+---------------+----------+-----------+
| 1 | table 1 row 1 | 1 | table 2 row 1 | 1 | 9
|
| 2 | table 1 row 2 | NULL | NULL | NULL | 0
|
| 3 | table 1 row 3 | 4 | table 2 row 3 | 3 | 3
|
+----------+---------------+----------+---------------+----------+-----------+

Is there any way of creating the query I want using Zend_Db_Select, or
should I just build it piecemeal myself?

Many thanks,
Mark

Test SQL:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
table1ID INT NOT NULL PRIMARY KEY,
someCol1 VARCHAR(32)
);

INSERT INTO table1 (table1ID,someCol1) VALUES
(1,'table 1 row 1'),
(2,'table 1 row 2'),
(3,'table 1 row 3')
;

DROP TABLE IF EXISTS table2;
CREATE TABLE table2 (
table2ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
someCol2 VARCHAR(32),
table1ID INT NOT NULL
);

INSERT INTO table2 (table2ID,someCol2,table1ID) VALUES
(1,'table 2 row 1',1),
(2,'table 2 row 2',1),
(3,'table 2 row 3',1),
(4,'table 2 row 3',3)
;

SELECT table1.table1ID,table1.someCol1,COUNT(table2ID) AS someCount
FROM table1
LEFT JOIN table2 USING (table1ID)
GROUP BY table1.table1ID;

--
View this message in context: http://www.nabble.com/Queries-with-LEFT-JOIN--COUNT%28table.column%29-possible-with-Zend_Db_Query--tp22557518p22557518.html
Sent from the Zend DB mailing list archive at Nabble.com.