PostgreSQL - Querying Second Table with Multiple Results

Question

I am working in PostgreSQL 9.6.6 on a query that involves the use of a second table to return additional data points. The table - "events" - is associated with many entries from the second - "activities" - via a foreign key.

Events

| id | name   |
|----|--------|
| 1  | event1 |
| 2  | event2 |
| 3  | event3 |

Activities

| id | name      | startDate              | endDate                | status    | eventId |
|----|-----------|------------------------|------------------------|-----------|---------|
| 1  | activity1 | 2018-08-27 05:00:00+00 | 2018-08-28 05:00:00+00 | Draft     | 1       |
| 2  | activity2 | 2018-09-27 05:00:00+00 | 2018-09-27 10:00:00+00 | Submitted | 1       |
| 3  | activity3 | 2018-08-25 05:00:00+00 | 2018-08-27 10:00:00+00 | Draft     | 1       |
| 4  | activity4 | 2018-08-21 05:00:00+00 | 2018-08-24 05:00:00+00 | Approved  | 2       |
| 5  | activity5 | 2018-09-27 05:00:00+00 | 2018-09-29 05:00:00+00 | Draft     | 2       |
| 6  | activity6 | 2018-10-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Approved  | 3       |
| 7  | activity7 | 2018-08-27 05:00:00+00 | 2018-08-27 10:00:00+00 | Approved  | 3       |

The data points being collected via the event are a startDate, endDate & status based on the associated activities.

startDate => The least startDate of all associated activities.
endDate => The greatest endDate of all associated activities.
status => Status equals "Pending" unless all associated activities have a status of "Approved" (then status equals "Complete").

An example returned query would look like:

| id | name   | startDate              | endDate                | status    |
|----|--------|------------------------|------------------------|-----------|
| 1  | event1 | 2018-08-25 05:00:00+00 | 2018-09-27 10:00:00+00 | Pending   |
| 2  | event2 | 2018-08-21 05:00:00+00 | 2018-09-29 05:00:00+00 | Pending   |
| 3  | event3 | 2018-08-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Completed |

I have formulated the following with multiple subqueries, but the performance is terrible.

How can I improve this to limit (or even remove) the use of subqueries?

SELECT DISTINCT ON("startDate", "event"."name") 1, "event"."id", "event"."name", (
  SELECT "activities"."startDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."startDate" ASC
  LIMIT 1
) AS "startDate", (
  SELECT "activities"."endDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."endDate" DESC
  LIMIT 1
) AS "endDate", (
  SELECT DISTINCT ON("event"."id")
    CASE "activities"."status"
    WHEN 'Draft' THEN 'Pending'
    WHEN 'Submitted' THEN 'Pending'
    ELSE 'Complete'
    END
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."status" DESC
) AS "status"
FROM "events" AS "event"
ORDER BY "startDate" DESC, "event"."name" ASC
LIMIT 20
OFFSET 0;

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2018-08-29 10:59:12 +00:00
Commented Aug 29, 2018 at 10:59 — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2018 at 10:59
@GordonLinoff - database is PostgreSQL 9.6.6

proph3t
– proph3t

2018-08-29 11:17:01 +00:00
Commented Aug 29, 2018 at 11:17 — proph3t
– proph3t, Commented Aug 29, 2018 at 11:17

Gordon Linoff · Accepted Answer · 2018-08-29 12:27:23Z

1

I think you want aggregation. Your logic and results suggests:

select eventid, min(startDate), max(endDate),
       (case when min(status) = max(status) and min(status) = 'Approved'
             then 'Approved'
             else 'Pending'
        end) as status
from activities a
group by eventid;

edited Aug 29, 2018 at 12:27

answered Aug 29, 2018 at 11:02

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

Gordon, I think you need to GROUP BY here or something; your current query would just generate a single record.

Collectives™ on Stack Overflow

PostgreSQL - Querying Second Table with Multiple Results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related