Subquery to count related records

[massiws]

Hi,
I read in the wiki (http://wiki.nubuilder.com/tiki-index.ph ... uilderDocs):
"DO NOT use a SELECT statement inside another SELECT statement here, because nuBuilder can't handle it."

There's a way to bypass this limitation for a query like this?

Code: Select all

SELECT *, (SELECT COUNT(*) FROM table2 WHERE table1_id = table2_table1_id) AS num FROM table1

The only way I found was to replace the SQL statement in the form with:

Code: Select all

SELECT * FROM #browseTable# 

and put this code in the CustomCode > BeforeBrowse:

Code: Select all

// Create a temporary table with the result of the first query
$query = nuRunQuery('CREATE TABLE #browseTable# SELECT * FROM table1');

// Add a column to temp table just created
nuRunQuery('ALTER TABLE #browseTable# ADD num INTEGER(5) NOT NULL');

// Select all record from temp table again
$rs = nuRunQuery('SELECT * FROM #browseTable#');

// For each record in table1, count how many related record in table2
while ($row = db_fetch_object($rs)) {
    $id = $row->table1_id;
    $num = db_fetch_row(nuRunQuery("SELECT COUNT(*) FROM table2 WHERE table2_table1_id = '$id' "));
    
// Insert the number of records in the new column
     nuRunQuery("UPDATE #browseTable# SET num = " . $num[0] . " WHERE table1_id = '$id' ");
}

// Select all record again to open the form
nuRunQuery('SELECT * FROM #browseTable# ORDER BY ....');

This works fine, but I wonder if there was a simpler solution.

Thank in advance.

[admin]

massiws,

Your Before Browse solution is the only way, unless you had a table being updated with the count when ever something changed.

Steven

[massiws]

Steven,

the only problem is on big tables: the number of queries needed may be high, but in this case the idea of storing total each time it changes is a good method.

Thank you very much.

[admin]

No worries