Create view with join depending on table of another join

Question

My tables:

create table Article (
  ID int generated by default as identity primary key, 
  Name text);
create table Client (
  ID int generated by default as identity primary key, 
  Name text);
create table Invoice (
  ID int generated by default as identity primary key, 
  Date date, 
  Client int references client(id));
create table InvoicePosition (
  ID int generated by default as identity primary key, 
  Invoice int references invoice(id), 
  Article int references article(id), 
  Quantity numeric, 
  Price numeric,
  constraint each_invoice_can_only_contain_one_invoiceposition_for_each_article
    unique (Invoice,Article));

Sample data:

insert into article(id,name) values
  (1,'article1'),
  (2,'article2'),
  (3,'article3');
insert into client(id,name) values
  (1,'client1'),
  (2,'client2'),
  (3,'client3');
insert into invoice(id,date,client) values
  (1,'yesterday',1),    -- Invoice by Client 1
  (2,'today',1),        -- Invoice by Client 1
  (3,'tomorrow',1),     -- Invoice by Client 1
  (4,'yesterday', 2);   -- Invoice by Client 2
insert into InvoicePosition values
  (1,1,1,11.0,77.0),    -- Invoice from yesterday by client 1 with article 1
  (2,1,2,22.0,88.0),    -- Invoice from yesterday by client 1 with article 2
  (3,1,3,33.0,99.0),    -- Invoice from yesterday by client 1 with article 3
  (4,2,1,12.0,78.0),    -- Invoice from today by client 1 with article 1
  (5,2,2,23.0,89.0),    -- Invoice from today by client 1 with article 2
  (6,4,1,34.0,100.0);   -- Invoice from yesterday by client 2 with article 1

Now I would like to select an InvoicePosition with details of the previous InvoicePosition (same client, same article). Basically:

• PreviousInvoicePosition.Article = InvoicePosition.Article
• PreviousInvoicePosition.Invoice.Client = InvoicePosition.Invoice.Client
• PreviousInvoicePosition.Invoice.Date < InvoicePosition.Invoice.Date
• Select only the first PreviousInvoicePosition, ordered by Date descending.

Ideally this should be created as a view so I can then select all InvoicePositions (including the PreviousInvoicePosition) for one invoice, using a WHERE clause.

Example select from created view:

// Invoice from today from client 1 with article 1
SELECT * FROM InvoicePosView WHERE InvoicePosId = 4

Expected result:

InvoicePosId = 4
InvoiceId = 2
ArticleID = 1
Quantity = 12.0
Price = 78.0
LastInvoicePosId = 1    // Invoice from yesterday from client 1 with article 1

If it matters: This tables and the view are part of a supabase project and are then queried using PostgREST.

Is this possible?

Answer 1

It sounds like you want the lag()over w1 window function. Demo:

create view invoices_with_previous as 
select ip.id       as "InvoicePosId",
       i.id        as "InvoiceId",
       ip.article  as "ArticleID",
       ip.quantity as "Quantity",
       ip.price    as "Price",
       lag(ip.id)over w1 as "LastInvoicePosId"
from InvoicePosition  as  ip 
  join invoice i on i.id =ip.invoice
window w1 as (partition by i.client, ip.article
              order by i.date)
order by ip.id;

select * from invoices_with_previous;

InvoicePosId	InvoiceId	ArticleID	Quantity	Price	LastInvoicePosId
1	1	1	11.0	77.0	null
2	1	2	22.0	88.0	null
3	1	3	33.0	99.0	null
4	2	1	12.0	78.0	1
5	2	2	23.0	89.0	2
6	4	1	34.0	100.0	null