Re: Why does this array query fail?

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.