Home PostgreSQL - Parent > Child Ordering
Reply: 1

PostgreSQL - Parent > Child Ordering

osmelg
1#
osmelg Published in 2017-09-13 18:06:57Z

Everytime i click the button (Parent Row),(the button action make a new row below (Child Row),this new row is an insert in the database).

My database has this cols, id - text1 - text2 - date

Solution needed

I need to have the sql to show the view side this parent child asociation with ASC of date childs.

Image https://www.picoolio.net/image/I6OQ

Andrei
2#
Andrei Reply to 2017-09-13 21:00:08Z

firstly you would have to have the database I'm going to create a generic one

-- this will create your table + primary key (i'm not sure what action data type is)
create table test (
 id serial PRIMARY KEY,
 id_parent integer,
 col1 text
)

on each insert you will be auto generating the id field because it is a serial and you wont need to add it in the insert statement

lets assume you already have root parents meaning that the id_parent value is null

root id = 1, 2, 3

  • INSERT INTO test (id_parent, col1) values (null, 'I'm parent 1')
  • INSERT INTO test (id_parent, col1) values (null, 'I'm parent 2')
  • INSERT INTO test (id_parent, col1) values (null, 'I'm parent 3')

so when you want to insert values you will simply run the query

INSERT INTO test (id_parent, col1) values (1, 'parent id is 1')

the above insert statement will auto generate the id field because it is a serial.

if we insert a few more records

  • INSERT INTO test (id_parent, col1) values (1, 'parent id is 1')
  • INSERT INTO test (id_parent, col1) values (1, 'second insert parent id is 1')
  • INSERT INTO test (id_parent, col1) values (2, 'first insert parent is 2')
  • INSERT INTO test (id_parent, col1) values (2, 'second insert parent id is 2')

you should have 7 records in the db now, the 3 root parents and the 4 children we have inserted

to retrieve your data you will want to reference back onto the parent column via join statement

select 
  parent.id
 ,child.id
 ,child.parent_id 
from test parent
  join test child = parent.id = child.id_parent
order by child.parent_id

you'll get something that looks like

parent.id | child.id | child.parnet_id
1         | 4        | 1
1         | 5        | 1
2         | 6        | 2
2         | 7        | 2

then if you do another insert for parent_id 1 via

  • INSERT INTO test (id_parent, col1) values (1, 'parent id is 1')

you'll get

parent.id | child.id | child.parnet_id
1         | 4        | 1
1         | 5        | 1
1         | 8        | 1
2         | 6        | 2
2         | 7        | 2

this is the general basics if you want to use the same table for a recursive join. I hope the helps? also I haven't tested any of the code so there might be a few syntax errors but that's the general idea

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.386512 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO