Re: Why does this array query fail?

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!)

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
--ORDER BY client_name(client_id)

On Tue, Sep 17, 2013 at 5:02 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> Ken Tanzer wrote
> > So I frequently have to provide information like "what were all the types
> > 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!
>
> Your example query does not ask those questions.
>
> 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.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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
*http://agency-software.org/*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.