Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE category (
  id         serial,
  parent_id  integer default null,
  tree_depth smallint NOT NULL,

  PRIMARY KEY (id),
  UNIQUE (parent_id, id),
  FOREIGN KEY (parent_id) REFERENCES category (id)
);

CREATE TABLE category_lang (
  id            serial,
  category_id   integer NOT NULL,
  lang_code     char(2) NOT NULL,
  title         varchar(128) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (category_id, lang_code)
--   foreign key (category_id) references category (id)  -- missing?
);

CREATE OR REPLACE FUNCTION pg_temp.fn_category__bins ()
  RETURNS trigger
  LANGUAGE plpgsql AS
$$
BEGIN
  -- calculate tree_depth as parent tree_depth + 1
  if new.parent_id is null then
    new.tree_depth = 0;
  else
    new.tree_depth = (select tree_depth from category where id = new.parent_id limit 1) + 1;
  end if;

  return new;
END
$$;

CREATE TRIGGER tg_category__bins
BEFORE INSERT ON category
FOR EACH ROW EXECUTE PROCEDURE pg_temp.fn_category__bins();

INSERT INTO category (parent_id, id) VALUES 
(null, 1),
(null, 2),
(null, 3),

(1, 11),
(1, 12),
(1, 13),

(2, 21),
(2, 22),
(3, 31),

(21, 211),
(21, 212),
(21, 213);

-- lang_code = 'EN'
INSERT INTO category_lang (category_id, title, lang_code) VALUES
(1, 'One',   'EN'),
(2, 'Two',   'EN'),
(3, 'Three', 'EN'),

(11, 'One.One',   'EN'),
(12, 'One.Two',   'EN'),
(13, 'One.Three', 'EN'),

(21, 'Two.One',   'EN'),
(22, 'Two.Two',   'EN'),
(31, 'Three.One', 'EN'),

(211, 'Two.One.One',   'EN'),
(212, 'Two.One.Two',   'EN'),
(213, 'Two.One.Three', 'EN');

-- lang_code = 'ES'
INSERT INTO category_lang (category_id, title, lang_code) VALUES
(1, 'Uno',  'ES'),
(2, 'Dos',  'ES'),
(3, 'Tres', 'ES'),

(11, 'Uno.Uno',  'ES'),
(12, 'Uno.Dos',  'ES'),
(13, 'Uno.Tres', 'ES'),

(21, 'Dos.Uno',  'ES'),
(22, 'Dos.Dos',  'ES'),
(31, 'Tres.Uno', 'ES'),

(211, 'Dos.Uno.Uno',  'ES'),
(212, 'Dos.Uno.Dos',  'ES'),
(213, 'Dos.Uno.Tres', 'ES');

CREATE TABLE



CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER



INSERT 0 12



INSERT 0 12



INSERT 0 12


-- once per database
CREATE TYPE title_id AS (title varchar(128), id int);

CREATE TYPE


-- EN
WITH RECURSIVE tree AS (
   SELECT c.id AS cat_id, c.parent_id, c.tree_depth
        , l.id AS lang_id, l.title, l.lang_code
        , ARRAY[(l.title, l.category_id)::title_id] AS sort_arr
   FROM   category      c 
   JOIN   category_lang l ON l.category_id = c.id
                         AND l.lang_code = 'EN'
   WHERE  c.parent_id IS NULL  -- root cat

   UNION ALL
   SELECT c.id AS cat_id, c.parent_id, c.tree_depth
        , l.id AS lang_id, l.title, l.lang_code
        , sort_arr || (l.title, l.category_id)::title_id
   FROM   tree          t
   JOIN   category      c ON c.parent_id = t.cat_id
   JOIN   category_lang l ON l.category_id = c.id
                         AND l.lang_code = t.lang_code
    )
SELECT cat_id, parent_id, tree_depth, lang_id, title 
FROM   tree
ORDER  BY sort_arr;


cat_id
parent_id
tree_depth
lang_id
title


1
null
0
1
One

11
1
1
4
One.One

13
1
1
6
One.Three

12
1
1
5
One.Two

3
null
0
3
Three

31
3
1
9
Three.One

2
null
0
2
Two

21
2
1
7
Two.One

211
21
2
10
Two.One.One

213
21
2
12
Two.One.Three

212
21
2
11
Two.One.Two

22
2
1
8
Two.Two



SELECT 12


-- ES
WITH RECURSIVE tree AS (
   SELECT c.id AS cat_id, c.parent_id, c.tree_depth
        , l.id AS lang_id, l.title, l.lang_code
        , ARRAY[(l.title, l.category_id)::title_id] AS sort_arr
   FROM   category      c 
   JOIN   category_lang l ON l.category_id = c.id
                         AND l.lang_code = 'ES'  -- provide once
   WHERE  c.parent_id IS NULL  -- root cat

   UNION ALL
   SELECT c.id AS cat_id, c.parent_id, c.tree_depth
        , l.id AS lang_id, l.title, l.lang_code
        , sort_arr || (l.title, l.category_id)::title_id
   FROM   tree          t
   JOIN   category      c ON c.parent_id = t.cat_id
   JOIN   category_lang l ON l.category_id = c.id
                         AND l.lang_code = t.lang_code
    )
SELECT cat_id, parent_id, tree_depth, lang_id, title 
FROM   tree
ORDER  BY sort_arr;


cat_id
parent_id
tree_depth
lang_id
title


2
null
0
14
Dos

22
2
1
20
Dos.Dos

21
2
1
19
Dos.Uno

212
21
2
23
Dos.Uno.Dos

213
21
2
24
Dos.Uno.Tres

211
21
2
22
Dos.Uno.Uno

3
null
0
15
Tres

31
3
1
21
Tres.Uno

1
null
0
13
Uno

12
1
1
17
Uno.Dos

13
1
1
18
Uno.Tres

11
1
1
16
Uno.Uno



SELECT 12

cat_id	parent_id	tree_depth	lang_id	title
1	null	0	1	One
11	1	1	4	One.One
13	1	1	6	One.Three
12	1	1	5	One.Two
3	null	0	3	Three
31	3	1	9	Three.One
2	null	0	2	Two
21	2	1	7	Two.One
211	21	2	10	Two.One.One
213	21	2	12	Two.One.Three
212	21	2	11	Two.One.Two
22	2	1	8	Two.Two

cat_id	parent_id	tree_depth	lang_id	title
2	null	0	14	Dos
22	2	1	20	Dos.Dos
21	2	1	19	Dos.Uno
212	21	2	23	Dos.Uno.Dos
213	21	2	24	Dos.Uno.Tres
211	21	2	22	Dos.Uno.Uno
3	null	0	15	Tres
31	3	1	21	Tres.Uno
1	null	0	13	Uno
12	1	1	17	Uno.Dos
13	1	1	18	Uno.Tres
11	1	1	16	Uno.Uno