Re: Why does this array query fail? - Mailing list pgsql-general
Based on what you described, I think I've generally gone with option A. Conceptually I like B better, but it's generally more complicated and seems like overkill for simple checkbox-type options. (But as an aside, I am looking forward to the time when ELEMENT FKs overcome their performance issues and become part of Postgres!)
--ORDER BY client_name(client_id)
The trouble seems to be that even with Option A (services_codes in an array within an encounter record), you still kind of end up with option C on a client level:
client {service_codes}
client {service_codes}
There may be no way around it, but it seems like you end up needing to write rather cumbersome queries to get at your data. OTOH there's always room for improvement; since I'm relatively new to working extensively with arrays, I'm hoping they become more intuitive and less painful as one gets used to them. :)
SELECT client_id,
COALESCE(
(SELECT array_agg(code) FROM (
SELECT distinct
client_id,unnest(accessed_health_care_non_urgent_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-08-01' AND '2013-08-31'
) foo
),array['(none)'])
AS accessed_health_care_non_urgent_codes
FROM client;
It's probably way more detail than you want, but I've attached the table structure and pasted in a quarterly report that the query above was taken from in case you have any pointers or are simply curious.
Thanks again!
Ken
/*
CREATE OR REPLACE VIEW hch_quarterly AS
*/
SELECT *
FROM (
SELECT
export_id,
UPPER(SUBSTRING(name_last,1,2) || SUBSTRING(name_first,1,2) || COALESCE(to_char(dob,'MMDDYY'),'')) AS hch_id,
name_last,
name_first,
dob,
'2013-01-01' AS quarter_start_date,
'2013-03-31' AS quarter_end_date,
referral_source_code || COALESCE(' (' || referral_source_other || ')','') AS referral_source,
facility_code AS living_situation_end,
/*
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(other_living_situation_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS other_sleeping_codes,
*/
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(other_living_situation_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31'
UNION SELECT distinct client_id,moved_from_code AS code
FROM residence_other
WHERE client_id=client.client_id
AND residence_date BETWEEN '2013-01-01' AND '2013-03-31' ) foo
),array['(none)'])) AS other_sleeping_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(accessed_health_care_non_urgent_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS accessed_health_care_non_urgent_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(accessed_services_cd_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS accessed_services_cd_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(completed_services_cd_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS completed_services_cd_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(accessed_services_mh_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS accessed_services_mh_codes,
CASE WHEN client_id IN (SELECT DISTINCT ON (client_id,staff_assign_date) client_id
FROM staff_assign
WHERE staff_assign_type_code='PAYEE' AND staff_assign_date<='2013-03-31' AND COALESCE(staff_assign_date_end,'2013-03-31') >= '2013-01-01'
ORDER BY client_id,staff_assign_date DESC) THEN 'Yes' ELSE 'No' END AS has_payee,
CASE WHEN client_id IN (SELECT client_id
FROM staff_assign
WHERE staff_assign_type_code='OUTREACH' AND staff_assign_date<='2013-03-31' AND COALESCE(staff_assign_date_end,'2013-03-31') >= '2013-01-01'
ORDER BY client_id,staff_assign_date DESC) THEN 'Yes' ELSE 'No' END AS outreach_client,
CASE WHEN client_id IN (SELECT client_id
FROM staff_assign
WHERE staff_assign_type_code='CM' AND staff_assign_date<='2013-03-31' AND COALESCE(staff_assign_date_end,'2013-03-31') >= '2013-01-01'
ORDER BY client_id,staff_assign_date DESC) THEN 'Yes' ELSE 'No' END AS cm_client,
service_plan_status_code
FROM client
LEFT JOIN (SELECT DISTINCT ON (client_id) *
FROM intake_reach
WHERE intake_reach_date <= '2013-03-31'
ORDER BY client_id,intake_reach_date DESC ) AS ir USING (client_id)
LEFT JOIN (SELECT DISTINCT ON (client_id) *
FROM residence_other
WHERE residence_date <= '2013-03-31' AND COALESCE(residence_date_end,'2013-03-31')>='2013-01-01'
ORDER BY client_id,residence_date DESC) AS ro USING (client_id)
LEFT JOIN (SELECT DISTINCT ON (client_id) *
FROM service_reach
WHERE service_date BETWEEN '2013-01-01' AND '2013-03-31'
ORDER BY client_id,service_date DESC) AS sr USING (client_id)
LEFT JOIN (SELECT client_id,export_id
FROM client_export_id
WHERE export_organization_code='HCH') exp USING (client_id)
WHERE client_id IN (SELECT client_id
FROM staff_assign
WHERE staff_assign_date <= '2013-03-31' AND staff_assign_type_code IN ('CM','OUTREACH') AND COALESCE(staff_assign_date_end,'2013-03-31') >= '2013-01-01' AND staff_project(staff_id) IN ('OUTREACH','REACH'))
) AS whole_shebang
On Tue, Sep 17, 2013 at 5:02 PM, David Johnston <polobo@yahoo.com> wrote:
Ken Tanzer wrote> So I frequently have to provide information like "what were all the typesYour example query does not ask those questions.
> of services this client received during the last quarter?" or "show me all
> the clients who received service X last year." I've learned enough to use
> ANY, array_agg and unnest to get through these queries, but if I'm going
> about this wrong or there's a better way to do it I'd love to know about
> it!
SELECT DISTINCT service_code
FROM (SELECT unnest(services_rendered_array) AS service_code FROM
services_tables WHERE ...) svcs;
SELECT DISTINCT client_id FROM (
SELECT * FROM services_table WHERE 'X' = ANY(services_rendered_array)
) svcs;
In neither case do you need to use a sub-query answer the question. Namely,
what you describe makes use of arrays only, and not relations (though the
allowed array item values could be defined on a table somewhere).
Option A:
A. T1: session_id, client_id, service_codes[], date
Note that A is the basic structured assumed for the two example queries
above.
Option B:
B. T1: session_id, session_date, client_id
B. T2: session_id (FK-many), service_code
B. T2 would have a single record for each service performed within a given
session while A. T1 models the multiple service aspect of a session by using
an array.
Incorrect Option C:
C. T1: session_id, session_date, client_id
C. T2: session_id, service_codes[]
This makes use of a one-to-many relationship but also embeds yet another
"many" aspect within C. T2 This is generally going to be a bad idea as you
are now mixing the models together. And note that I do qualify this as
generally since you may very well decide that C is an elegant and/or the
most correct way to model your domain.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771359.htmlSent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
AGENCY Software
A data system that puts you in control
100% Free Software
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Attachment
pgsql-general by date: