Skip to content

Instantly share code, notes, and snippets.

@afidegnum
Created August 11, 2017 17:43
Show Gist options
  • Save afidegnum/770782ae4135bf54b199461fa209bbda to your computer and use it in GitHub Desktop.
Save afidegnum/770782ae4135bf54b199461fa209bbda to your computer and use it in GitHub Desktop.
link tree and related table
I have 2 tables.
category and products. [from google product](https://www.google.com/basepages/producttype/taxonomy.en-US.txt) taxonomy tree i was able to reconstruct the entire tree.
i have the following tables,
```
CREATE TABLE public.category
(
id integer NOT NULL DEFAULT nextval('category_id_seq'::regclass),
name character varying(400),
image text,
rgt integer NOT NULL,
parent_id integer,
level integer NOT NULL,
tree_id integer,
lft integer NOT NULL,
CONSTRAINT category_pkey PRIMARY KEY (id),
CONSTRAINT category_parent_id_fkey FOREIGN KEY (parent_id)
REFERENCES public.category (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
```
and the following `catalog` table,
```
CREATE TABLE public.catalog
(
id integer NOT NULL DEFAULT nextval('catalog_id_seq'::regclass),
name character varying(300),
category_id integer,
description text,
CONSTRAINT catalog_pkey PRIMARY KEY (id),
CONSTRAINT catalog_category_id_fkey FOREIGN KEY (category_id)
REFERENCES public.category (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT catalog_color_id_fkey FOREIGN KEY (color_id)
REFERENCES public.color (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT catalog_size_id_fkey FOREIGN KEY (size_id)
REFERENCES public.size (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.catalog
OWNER TO ladies;
```
how do i list category trees if only the child's category.id is == catalog.category_id ?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment