CODEWITHSUNDEEP
postgresql
Aleksey Kontsevich
Aleksey Kontsevich

Reputation: 5011

Order by objects relation (PostgreSQL)

Have 2 tables for example:

In 1st: object & parent columns

object | parent 
-------+---------
object1| null       
object2| object1  
object3| null

2nd has: object & reference columns

object | reference
-------+---------
object1| null       
object2| null       
object3| object1

Need to query tables to order like following: parent is first, then - child(s), objects which have reference(s) to parent. 

object1
object2
object3

Is it possible to do in one SQL query or need to sort manually in an array? Seems it is a classical task, probably solution already exists somewhere?

Upvotes: 1

Views: 1113

Answers (4)

Aleksey Kontsevich
Aleksey Kontsevich

Reputation: 5011

Following recursive query works:

WITH RECURSIVE tables(object, rank) AS (
    SELECT DISTINCT o.object, 1 AS rank FROM oref o
    WHERE o.ref IS NULL
    UNION
    SELECT o.object, t.rank + 1 AS rank
    FROM (SELECT DISTINCT o.object, o.ref FROM oref o
            WHERE ref IS NOT NULL) o, tables t
    WHERE o.ref = t.object AND rank <= t.rank
),
ordered AS (
    SELECT * FROM tables
)
SELECT * FROM tables 
WHERE tables.rank = (SELECT MAX(rank) FROM ordered WHERE ordered.object = tables.object)
ORDER BY rank;

Any comments, questions, objections, propositions? ;)

Upvotes: 0

Aleksey Kontsevich
Aleksey Kontsevich

Reputation: 5011

No, does not work: checked for another data and simplified to use and only by oref table content:

INSERT INTO oref VALUES
('object1',null),('object2',null),('object3','object1'),
('object5','object6'),('object6','object1'),('object7','object4'), ('object4','object5');

WITH family AS (
    SELECT object AS obj, 1 AS rank FROM oref
     WHERE ref IS NULL
    ),
hier AS (
    SELECT * FROM family
    UNION ALL
    SELECT object AS obj, coalesce(f.rank + 2, 5) AS rank
      FROM oref LEFT JOIN family f ON oref.ref = f.obj
     WHERE ref IS NOT NULL
    ),
allobj AS (
    SELECT object AS obj FROM oref)
SELECT a.obj, h.rank AS rank
  FROM allobj a
  LEFT JOIN hier h ON a.obj = h.obj
 ORDER BY h.rank, a.obj;

Think need to use recursive queries here. Will write and post here.

Upvotes: 0

vyegorov
vyegorov

Reputation: 22895

Is this what you're looking for?

CREATE TABLE oparen (object varchar(10), parent varchar(10));
CREATE TABLE oref (object varchar(10), ref varchar(10));
INSERT INTO oparen VALUES
    ('object1',null),('object2','object1'),
    ('object3',null),('object4','object2');
INSERT INTO oref VALUES
    ('object1',null),('object2',null),('object3','object1'),
    ('object5','object6'),('object6','object1'),('object7','object4');

WITH hier AS (
    SELECT parent AS obj, 1 AS rank FROM oparen
     WHERE parent IS NOT NULL
    UNION
    SELECT object, 2 FROM oparen
     WHERE parent IS NOT NULL
    UNION
    SELECT object, 3 FROM oref
     WHERE ref IS NOT NULL),
allobj AS (
    SELECT object AS obj FROM oparen
    UNION
    SELECT object FROM oref)
SELECT a.obj, coalesce(h.rank, 4) AS rank
  FROM allobj a LEFT JOIN hier h ON a.obj = h.obj
 ORDER BY coalesce(h.rank, 4), a.obj;

EDIT: After the improved example in the answer below, the following query should do the trick:

WITH parents AS (
    SELECT parent AS obj, 1 AS rank FROM oparen
     WHERE parent IS NOT NULL
    ),
family AS (
    SELECT * FROM parents
    UNION ALL
    SELECT object, 2 FROM oparen op
     WHERE parent IS NOT NULL
       AND NOT EXISTS (SELECT obj FROM parents WHERE obj = op.object)
    ),
hier AS (
    SELECT * FROM family
    UNION ALL
    SELECT object AS obj, coalesce(f.rank + 2, 5) AS rank
      FROM oref LEFT JOIN family f ON oref.ref = f.obj
     WHERE ref IS NOT NULL
    ),
allobj AS (
    SELECT object AS obj FROM oparen
    UNION
    SELECT object FROM oref)
SELECT a.obj, h.rank AS rank
  FROM allobj a LEFT JOIN hier h ON a.obj = h.obj
 ORDER BY h.rank, a.obj;

Testbed creation in the top is updated according to the new requirements.

Upvotes: 1

Aleksey Kontsevich
Aleksey Kontsevich

Reputation: 5011

I inserted following data:

INSERT INTO oparen VALUES
    ('object1',null),('object2','object1'),('object3',null),('object4','object2');
INSERT INTO oref VALUES
    ('object1',null),('object2',null),('object3','object1'),('object5','object6'),('object6','object1');

Order is incorrect and object2 listed twice. DISTINCT on obj breaks the order also. Should go 6 then 5.

Upvotes: 0

Related Questions