Re: Function fixing - PostgreSQL 9.2

"drum.lucas@xxxxxxxxx" <drum.lucas@xxxxxxxxx> · Wed, 2 Mar 2016 12:33:04 +1300

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.

Just an update - Hope this help you to get more data:

There is a number beside each color flag, and a number in the "sub status" drop down. Instead of "the number of jobs that have billable items that can be invoiced, it shows the "the number of jobs that have "something" billable but not yet invoiced."
And since those payments cannot be invoiced alone, they won't show up in the list.

This is the Query:
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"; 