From: Josh Cheek <josh.cheek@...>
Date: 2009-09-15T13:56:40+09:00
Subject: Re: Pre-allocate large amount of memory?

--000e0cd6c8549a1f500473967705
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Sep 14, 2009 at 11:29 PM, Carsten Gehling <carsten@sarum.dk> wrote:

> Robert Klemme wrote:
>
> > Is your structure strictly hierarchical (i.e. a tree) or do you need to
> > query part of a graph with cycles?  If it is strictly hierarchical there
> > is a solution that works for all RDBMS and in the latter case there are
> > solutions for some RDBMS.
>
> Unfortunately it is not strictly hierarchial. It is relations between
> companies-companies and companies-persons, that represents shareholders,
> parent-/subsidiary companies, etc.
>
> My job is to - given a certain company X - to extract all its owners
> and, recursively, their owners, etc. until I reach the "top". Likewise
> the other way to extract all companies owned by company X and,
> recursively, all companies owned by them, etc.
>
> Conceptually, the table (actually a view) I am querying holds the data:
>
> CompanyA,  Direction, CompanyB,  Share
> "FooCorp", "owns",    "BarCorp", "10%"
> "BarCorp", "ownedby", "FooCorp", "10%"
> "BarCorp", "owns",    "BazCorp", "45%"
> "BasCorp", "ownedby", "BarCorp", "45%"
> "QweCorp", "owns",    "RteCorp", "20%"
> "RteCorp", "ownedby", "QweCorp", "20%"
> etc.
>
> The table is not of my doing. It is very difficult (at least for me) to
> devise a way to only query the nessecery rows in the table, without
> sorting to recursive calls.
>
> In the example above: If given "FooCorp", all but the last two rows
> should be extracted and used in the result. How would you go about doing
> that?
>
> I haven't found a solution yet. This is why I've gone and made a
> service, that holds all these data in memory (in a hash), to speed up on
> things.
>
> BTW: Thanks for all your great suggestions so far. :-)
>
> - Carsten
> --
> Posted via http://www.ruby-forum.com/.
>
>
As far as retrieving them all in a single query, nested sets would do this.
There are other hits that you take from using them, though. Changing the
hierarchy, for example, is very expensive (ie company a sells company b or
purchases company c), and treating it as a recursive structure after you
have it retrieved is quite painful. If you do opt for this solution, I can
send you my code to give recursive functionality through iteration. Though I
did change some lines in the plugin itself, to get more flexible use, but I
think I marked them all.

http://github.com/collectiveidea/awesome_nested_set

--000e0cd6c8549a1f500473967705--