NOTE: But this DB function got much faster if run directly on DB, without data reference entity data by spring hibernate code in Order entity.
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE FUNCTION "sample"."getpreviousorders"("_sampleid" varchar, "_mrn" varchar, "_createddate" timestamp)
- RETURNS SETOF "sample"."order" AS $BODY$
- BEGIN
- RETURN QUERY
- SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
- WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
- AND ((o1.collection_date is not null AND o1.collection_date >= _createdDate)
- OR (o1.collection_date is null AND o1.receipt_date is not null
- AND o1.receipt_date >= _createdDate))
- UNION
- (SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
- WHERE (o2.status = 1 or o2.status = 2 OR o2.status = 3) AND o2.sample_id != _sampleId
- AND p2.mrn = _mrn
- AND ((o2.collection_date is not null AND o2.collection_date < _createdDate)
- OR (o2.collection_date is null AND o2.receipt_date is not null
- AND o2.receipt_date < _createdDate)) ORDER by created_date DESC LIMIT 1)
- ORDER by collection_date DESC, receipt_date DESC, created_date DESC;
- END; $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000