sorting en calculating on internal relation

Questions, tips and tricks and techniques for scripting in Servoy

sorting en calculating on internal relation

Postby martijn » Mon Jun 27, 2016 10:21 am

We have a table with cargo details
it is build up like this

CargoID | CargoByID | CargoAmount | CargoPackaging | CargoWeight

the CargoByID contains the ID of a the upper detail

An example of the database would be

1 | 1 | 2 | pallet | 10
2 | 1 | 10 | boxes | 0,1
3 | 2 | 200 | boy toys | 0,1
4 | 1 | 5 | boxes | 0,1
5 | 4 | 250 | girl toys| 0,2

i have to make a view which gives a correct list of the packing like
1 pallet 10 boxes 200 boy toys
5 boxes 250 girl toys
at this moment I just have build a string with the id's in it separated by "-" to sort the table an calculate from there.

for record 1 the string would be just 1
for record 3 the string would be 1-2-3

then I can use a for loop

But there must be a smarter way
martijn
 
Posts: 18
Joined: Mon Jun 27, 2016 10:06 am

Re: sorting en calculating on internal relation

Postby ROCLASI » Mon Jun 27, 2016 5:06 pm

Hi Martijn,

Essentially you are talking about hierarchical data, i.e. trees.

Take a look at my presentation from ServoyWorld 2008 on this subject. It was inspired by this blog post from PostgreSQL community member Hubert Lubaczewski (a.k.a. Depesz).
It explains how to use an adjacency list to query your tree with 1 single query (and also sort).

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: sorting en calculating on internal relation

Postby martijn » Tue Jun 28, 2016 10:30 am

Hi ROCLASI,

certainly seems to represent my problem. Thanks for the link
martijn
 
Posts: 18
Joined: Mon Jun 27, 2016 10:06 am


Return to Methods

Who is online

Users browsing this forum: No registered users and 2 guests