Hierarchical Article Tag #111

New issue

Open

opened 2025-10-14 22:42:01 +00:00 by Ghost · 3 comments

Ghost commented

2025-10-14 22:42:01 +00:00

(Migrated from code.ole.blue)

![Image](https://github.com/user-attachments/assets/cce2c9fe-c70e-4b2f-90ec-92f851aa5c65) ![Image](https://github.com/user-attachments/assets/6cbcd0e0-be8d-430c-9bc2-2eb129a21868) ![Image](https://github.com/user-attachments/assets/8443e8b9-75b6-4513-bef8-2f2bed5a11c7)

Ghost commented

2025-11-11 23:17:16 +00:00

(Migrated from code.ole.blue)

To make the Tags frontend compatible, I would suggest, that only the leaf path is actually shown as the Category and the full path is only used while setting the Category.

E.g.:

/"drinks"/"Alkhoholfrei"/"Softdrinks" -> "Softdrinks"
/"drinks"/"Alkoholfrei"/"Wasser und Säfte" -> "Wasser und Säfte"
/"drinks"/"Alkoholhaltig"/"Bier" -> "Bier"

/"snacks"/"Erdnüsse"/ -> "Erdnüsse"
...

To make the Tags frontend compatible, I would suggest, that only the leaf path is actually shown as the Category and the full path is only used while setting the Category. E.g.: /"drinks"/"Alkhoholfrei"/"Softdrinks" -> "Softdrinks" /"drinks"/"Alkoholfrei"/"Wasser und Säfte" -> "Wasser und Säfte" /"drinks"/"Alkoholhaltig"/"Bier" -> "Bier" /"snacks"/"Erdnüsse"/ -> "Erdnüsse" ...

Ghost commented

2025-11-13 08:14:58 +00:00

(Migrated from code.ole.blue)

create table Tags (
	id integer not null,
	primary key (id)
);

create table TagsParentTagsMap(
	child integer not null,
	parent integer not null,

	foreign key(child) references Tags(id),
	foreign key(parent) references Tags(parent)

);

create table ArticleTagMap (
    article_id  integer not null,
    tag_id integer not null,
	foreign key(article_id) references Articles(id)
	foreign key(tag_id) references Tags(id)
);

```sql create table Tags ( id integer not null, primary key (id) ); create table TagsParentTagsMap( child integer not null, parent integer not null, foreign key(child) references Tags(id), foreign key(parent) references Tags(parent) ); create table ArticleTagMap ( article_id integer not null, tag_id integer not null, foreign key(article_id) references Articles(id) foreign key(tag_id) references Tags(id) ); ```

Ghost commented

2025-11-13 15:47:55 +00:00

(Migrated from code.ole.blue)

We need a path in the Tag

create table Tags (
    id integer not null,
    path varchar(255) not null,
    primary key (id)
);

Also TagsParentTagsMap does not allow a root tag, since all tags must have parents.
Why doesn't something like this work?

create table Tags(
    id integer not null,
    parent_id integer,
    path varchar(255) not null,
    primary key (id)
);

Then we wouldn't even need the TagsParentTagsMap table.

1. We need a path in the Tag ```sql create table Tags ( id integer not null, path varchar(255) not null, primary key (id) ); ``` 2. Also `TagsParentTagsMap` does not allow a root tag, since all tags must have parents. 3. Why doesn't something like this work? ```sql create table Tags( id integer not null, parent_id integer, path varchar(255) not null, primary key (id) ); ``` Then we wouldn't even need the `TagsParentTagsMap` table.