Find next consecutive row by order_num field

Question

I have 4 tables in a Postgres 9.5 DB for sending notices to customers with below table structure:

notices

id    name     
--------------
111   notice1  
112   notice2
113   notice3

notice_documents - a single notice can have multiple documents with incremented order_num.

id    notice_id   name   order_num    
----------------------------------
211   111         doc1   1
212   111         doc2   2
213   111         doc2   3
214   112         doc3   1
215   113         doc4   1
216   113         doc5   2
217   113         doc6   3
218   113         doc7   4

notice_details - this table have sent notice documents records. is_archived = 0 means it is active and consider those records only.

id   customer_id   notice_id   notice_document_id   is_archived
1    3133          111         211                  0
2    3133          111         212                  0
3    3134          112         214                  0
4    3135          113         216                  0

customers - each customer has a notice_id for which documents get sent.

id   customer_name notice_id
3133 abc           111
3134 xyz           112
3135 pqr           113

All columns are defined NOT NULL, and referential integrity is enforced with FK constraints.

I need to fetch the consecutive or next document for customers:

1. If I sent the first two documents (like order_num = 1 and 2) then next document will be the 3rd one (order_num = 3) - like for customer 3133 in the example.
2. If I directly sent a 2nd document (like order_num = 2 then next doc will also be the 3rd - like for customer 3135.
3. If the last document has already been sent then do not send any document.
4. If no document has been sent yet, then send 1st document.

I have tried to fetch the last inserted row in notice_details table with group by notice_id, customer_id and get the sent order_num but that won't cover the all scenarios.
I also tried to skip those rows that already have been sent but that also does not cover the scenario.

How could I manage that?

Answer 1

Assuming that notice_documents.id monotonically increases with order_num (which makes the column order_num redundant noise), this should satisfy all requirements:

SELECT DISTINCT ON (c.id, ndo.notice_id)
       c.id AS customer_id, notice_id, ndo.id AS notice_document_id
FROM   customers             c
JOIN   notice_documents ndo USING (notice_id)
WHERE  NOT EXISTS (
   SELECT 1
   FROM   notice_details 
   WHERE  customer_id = c.id
   AND    notice_id   = c.notice_id
   AND    is_archived  -- "consider those records only"
   AND    notice_document_id >= ndo.id
   )
ORDER  BY c.id, notice_id, ndo.id;

It returns the document with the next higher notice_document_id than highest document ID already sent for each combination of customer and notice. (Nothing where all documents have already been sent.)

If you have concurrent access, you need to do more to avoid race conditions. Related:

• How to perform conditional insert based on row count?

About DISTINCT ON:

• Select first row in each GROUP BY group?

How to exclude rows that are already referenced from another table:

• Select rows which are not present in other table

Simpler for a single given customer_id (addressing question update):

SELECT DISTINCT ON (notice_id)
       notice_id, ndo.id AS notice_document_id
FROM   customers        c
JOIN   notice_documents ndo USING (notice_id)
WHERE  c.customer_id = $customer_id  -- input customer here
WHERE  NOT EXISTS (
   SELECT 1
   FROM   notice_details 
   WHERE  customer_id = c.id
   AND    notice_id   = c.notice_id
   AND    is_archived  -- "consider those records only"
   AND    notice_document_id >= ndo.id
   )
ORDER  BY notice_id, ndo.id;