Re: Function fixing - PostgreSQL 9.2

Question:

Payments in a Pending state cannot be invoiced and are excluded from the Invoice Runs section, but they are showing in the count mechanic.

How can I solve this?

​In 9.2 you probably need to convert the count into a conditional sum:

SELECT sum(CASE WHEN <boolean> THEN 1 ELSE 0 END) FROM data;

You can probably do the same with count since it excludes nulls.

SELECT count(NULLIF(invoice_status, 'Pending') FROM invoices;

​9.4 introduced a FILTER clause for Aggregate Expressions that can do this much more cleanly and efficiently.​

​http://www.postgresql.org/docs/9.4/static/sql-expressions.html​

David J.

​I barely scanned your original query - just read the description.  I don't have the inclination - especially without a self-contained example - to make changes to it.

David J.

​

 

public function search($type=self::SEARCH_TYPE_STATUS_TYPE, &$rawData = []){
if($type === self::SEARCH_TYPE_STATUS_TYPE) {
$fields = "
stj.status_type_id,
stj.jobs_count,
stj.job_ids,
(
SELECT
array_to_json(array_agg(srcs))
FROM
(
-- property names in the json match column names in g_statuses_jobs()
SELECT
(srs.sr[1]::BIGINT) AS status_id,
(srs.sr[2]::TEXT) AS status_label,
(srs.sr[3]::BOOLEAN) AS status_is_default,
(srs.sr[4]::BIGINT) AS jobs_count,
(srs.sr[5]::JSON) AS job_ids
FROM
(SELECT regexp_split_to_array(regexp_split_to_table(stj.status_type_data, E'\n'), E'\t')) AS srs(sr)
) AS srcs
) AS status_type_json
";
$searchFunction =  'g_status_types_jobs($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25) AS stj';
$factory = new JobSearchStatusSummaryFactory();
}else{
$fields = '*';
$searchFunction =  "g_statuses_jobs($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25)";
$factory = new JobSearchResultFactory();
}
$query = "SELECT
$fields
FROM $searchFunction";