PostgreSQL - Multiple Parent Corregido

   
Vista:
Imágen de perfil de outrera

Multiple Parent Corregido

Publicado por outrera (35 intervenciones) el 02/04/2014 16:44:05
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
create table category (
  id serial primary key,
  name text not null,
  parent_id int null references category(id)
);
 
 
create table brand (
  id serial primary key,
  name text not null unique
);
 
 
create table brand_category (
  brand_id int not null references brand(id),
  category_id int not null references category(id),
  primary key (brand_id, category_id)
);
 
 
/* the product specification, not the actual product asset instance: */
create table product (
  id serial primary key,
  name text not null,
  brand_id int not null references brand(id)
);
 
 
create table product_category (
  product_id int not null references product(id),
  category_id int not null references category(id),
  primary key (product_id, category_id)
);
 
 
 
 
 
/* null parent_id means top-level category */
insert into category values
 
(1, 'Electronics', null),
(2, 'Cameras', null),
 
(3, 'Smart Phones', 1),
(4, 'Tablets', 1),
(5, 'Laptops', 1),
(6, 'Headphones', 1);
 
 
insert into brand values
(1, 'Samsung'),
(2, 'Nokia'),
(3, 'Apple');
 
 
/* samsung brand in both smartphones and cameras: */
insert into brand_category values
(1, 3),
(1, 2),
(2, 3),
(3, 3);
 
 
insert into product values
(1, 'Samsung Model X', 1);
 
 
/* add samsung model x as a smart phone: */
insert into product_category values
(1, 3);
 
 
 
-- Suppose we want to traverse upwards from product to see the full classification. The query would look like:
 
WITH RECURSIVE product_chain (id, path, parent_id, category_name) AS (
   SELECT c.id, c.name, parent_id, c.name
     FROM category c
    WHERE id in (SELECT category_id FROM product_category
                  WHERE product_id = 1)
UNION ALL
   SELECT c.id, c.name || '/' || pc.path, c.parent_id, c.name
     FROM category c
     JOIN product_chain pc ON pc.parent_id = c.id
 )
 SELECT p.*, pc.path
   FROM product p
   JOIN product_chain pc ON pc.parent_id IS NULL
  WHERE p.id = 1;
 
 
 
--Similarly we can generate the entire category tree with something like:
 
WITH RECURSIVE cat_tree (id, level, path) AS (
   SELECT c.id, 1, c.name
     FROM category c WHERE parent_id IS NULL
UNION ALL
   SELECT c.id, ct.level + 1, ct.path || '/' || c.name
     FROM category c
     JOIN cat_tree ct ON c.parent_id = ct.id
 )
 select c.name, ct.path, ct.level
   FROM category c
   JOIN cat_tree ct ON c.id = ct.id;
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder