COUNT GROUP BY

[rafig]

Just a quick 'heads up' to make sure there are 'spaces' in queries that are split over multiple lines, so maybe add an extra space before each '\', as I had a query that worked in a SQL query tool, but not in Servoy until I made sure there were spaces in all the right places

[martijn]

Solved
working code:

var var_sql = "UPDATE tbl_offers SET tbl_offer_goods_count = ( \
SELECT AMOUNTGOODS \
FROM ( \
SELECT COUNT(TEMP.offer) AS AMOUNTGOODS \
, TEMP.offer AS OFFERID \
FROM ( \
SELECT DISTINCT ON (tbl_offer_cargo.tbl_offer_cargo_good_id) \
tbl_offer_cargo.tbl_offer_cargo_offerid as offer \
FROM public.tbl_offer_cargo, public.tbl_offers \
WHERE tbl_offers.tbl_offer_id = tbl_offer_cargo.tbl_offer_cargo_offerid \
) TEMP \
WHERE TEMP.offer = " + tbl_offer_cargo_offerid +
"GROUP BY offer \
) TEMP2 \
)WHERE tbl_offers.tbl_offer_id = " + tbl_offer_cargo_offerid;
plugins.rawSQL.executeSQL("transport_db","tbl_offers",var_sql)
databaseManager.refreshRecordFromDatabase(forms.frm_offers.foundset,-1)

Thank you all for helping

[mboegem]

Two recommended improvements to your implementation:
1) pass the id as parameter to the query, rather than concatenate.
This will make PG see each or these queries as 'similar' but with different params, rather than numerous unique queries.
From a security point of view, using params will prevent sql injection.

2) To make your solution work in a multi user environment, you should broadcast the change rather than refreshing the record from database.
Refreshing the record from database will only reflect to the current user, where a broadcast will trigger all clients to update the foundset (and thus the record).

Taking your solution, this would be the improved version:

Code: Select all

var var_sql = "UPDATE tbl_offers SET tbl_offer_goods_count = ( \
SELECT AMOUNTGOODS \
FROM ( \
SELECT COUNT(TEMP.offer) AS AMOUNTGOODS \
, TEMP.offer AS OFFERID \
FROM ( \
SELECT DISTINCT ON (tbl_offer_cargo.tbl_offer_cargo_good_id) \
tbl_offer_cargo.tbl_offer_cargo_offerid as offer \
FROM public.tbl_offer_cargo, public.tbl_offers \
WHERE tbl_offers.tbl_offer_id = tbl_offer_cargo.tbl_offer_cargo_offerid \
) TEMP \
WHERE TEMP.offer = ? \
GROUP BY offer \
) TEMP2 \
) WHERE tbl_offers.tbl_offer_id = ?";
var var_params = [tbl_offer_cargo_offerid, tbl_offer_cargo_offerid];
var var_result = plugins.rawSQL.executeSQL("transport_db","tbl_offers",var_sql, var_params);
if(var_result) {
   plugins.rawSQL.flushAllClientsCache("transport_db","tbl_offers");
}


[martijn]

great tip, but for the

1) pass the id as parameter to the query, rather than concatenate.
This will make PG see each or these queries as 'similar' but with different params, rather than numerous unique queries.
From a security point of view, using params will prevent sql injection.

params does not seem to be expected, because I get a false result with these changes

[mboegem]

I get a false result with these changes

Did you replace your query with my example?
The query can not work using the question marks and no params.

Something else that came to mind:
It seems to me that you're executing this query on the form which has the 'tbl_offers' record active that you need to update with this count.
In that case, from a performance point of view, it would be easier/better to query the count using databaseManager.getDatasetByQuery.
Then set the result into the tbl_offer_goods_count column, and call databaseManager.saveData();

This will automatically trigger the databroadcast that you need in a multi-user environment, but a less 'expensive' operation than 'plugins.rawSQL.flushAllClientsCache()'

This would look like this:

Code: Select all

var var_sql = "SELECT AMOUNTGOODS \
FROM ( \
SELECT COUNT(TEMP.offer) AS AMOUNTGOODS \
, TEMP.offer AS OFFERID \
FROM ( \
SELECT DISTINCT ON (tbl_offer_cargo.tbl_offer_cargo_good_id) \
tbl_offer_cargo.tbl_offer_cargo_offerid as offer \
FROM public.tbl_offer_cargo, public.tbl_offers \
WHERE tbl_offers.tbl_offer_id = tbl_offer_cargo.tbl_offer_cargo_offerid \
) TEMP \
WHERE TEMP.offer = ? \
GROUP BY offer";
var var_params = [tbl_offer_cargo_offerid];
var var_result = databaseManager.getDataSetByQuery("transport_db",var_sql, var_params,-1);
foundset.tbl_offer_goods_count = var_result.getValue(1,1);
databaseManager.saveData();