CODEWITHSUNDEEP
postgresql
Sławosz
Sławosz

Reputation: 11687

How can I parse query result to string in postgres?

I prepared sql fiddle: http://sqlfiddle.com/#!15/62e65/2

And schema is here:

CREATE TABLE products
    ("id" int, "name" varchar(5))
;

INSERT INTO products
    ("id", "name")
VALUES
    (1, 'Car'),
    (2, 'Phone')
;

CREATE TABLE operations
    ("id" int, "product_id" int, "status" varchar(7), "type" varchar(8))
;

INSERT INTO operations
    ("id", "product_id", "status", "type")
VALUES
    (1, 1, 'pending', 'invoice'),
    (2, 1, 'done', 'delivery'),
    (3, 2, 'done', 'delivery'),
    (3, 2, 'done', 'invoice')
;

I know that the data schema could be better, but I don't have possibility to refactor it now - I am just adding new view. Note about schema: product has always 2 operations: invoicing and delivery. What I want to achieve is to get such result:

name status
car pending
phone done

Where product status is a string returned after checking both product operations.
Rule is that product status is done only when both operations are done, otherwise its pending.
How to write such query in postgres?

Upvotes: 1

Views: 1043

Answers (3)

Gabriel's Messanger
Gabriel's Messanger

Reputation: 3298

SELECT p.name, CASE WHEN status='done' THEN 'done' else 'pending' END
FROM (
    SELECT p.id, p.name, string_agg(distinct o.status, '') as status
    FROM products p JOIN operations o ON o.product_id = p.id
    GROUP BY p.id, p.name
) sub

Fisrtly we concatenate distinct values in one string (string_agg) and then in master query we check: if ne string is single 'done' that meand that all operations were 'done'. Otherwise one or both operation are 'pending'.

Since your foreign key is products.id <--> operations.product_id we must GROUP BY products.id.

Upvotes: 1

stas.yaranov
stas.yaranov

Reputation: 1797

select
    p.name,
    case
        when count(case when status <> 'done' then 1 else null end) = 0 then 'done'
        else 'pending'
    end status
  from products p
  inner join operations o on p.id = o.product_id
  group by p.name, p.id;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311063

You could create two subqueries, one for orders and one for deliveries, join them, and then use a case expression to check the status on both:

SELECT invoice.name, 
       CASE WHEN invoice.status = 'done' and delivery.status = 'done' THEN 'done' 
            ELSE 'pending' 
       END AS status
FROM   (SELECT p.name, p.id, o.status
        FROM   products p
        JOIN   operations o ON o.type = 'invoice' AND 
                               o.product_id = p.id) invoice
JOIN   (SELECT p.name, p.id, o.status
        FROM   products p
        JOIN   operations o ON o.type = 'delivery' AND
                               o.product_id = p.id) delivery
       ON invoice.id = delivery.id

Upvotes: 0

Related Questions