Re: Recursive Arrays 101

I'm sorry, I don't know exactly what you mean by "definitions." The fields
Taxon and Parent are both varchar, with a 50-character limit. ParentID is
int(1).

Here's a discussion that describes the table in a little more detail --
http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii

And this is the discussion where someone suggested I check out PostgreSQL
--
http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure

On Sun, Oct 25, 2015 at 10:59 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 10/25/2015 08:48 AM, David Blomstrom wrote:
>
>> I'm creating a website focusing on living things (mostly animals). I
>> have multiple huge MySQL database tables with animal taxons arranged in
>> a parent-child relationship. I was trying to figure out how I could
>> navigate to a URL like MySite/life/mammals and display the number of
>> children (i.e. orders), grandchildren (families), great grandchildren
>> (genera) and great great grand children (species).
>>
>> I was then steered towards some sort of MySQL substitute for a full
>> outer join (which can apparently only be done in Postgre), followed by
>> an introduction to stored procedures. Pretty complicated stuff.
>>
>> Then someone told me it's stupid to jump through all those hoops when
>> you can easily do that sort of thing with Postgre.
>>
>> So that's my specific goal - to set up my animals website so it can
>> quickly and efficiently calculate and display things like grandchildren,
>> great grandparents, the number of children that are extinct, etc.
>>
>> My database tables look something like this, where Taxon, Parent and
>> ParentID are the names of the key fields:
>>
>> Taxon | Parent | ParentID
>> Animalia | Life | (NULL)
>> Chordata | Animalia | (NULL)
>> Animalia | Chordata | 0
>> Mammalia | Animalia | 1
>> Carnivora | Mammalia | 2
>> Felidae | Carnivora | 3
>> Panthera | Felidae | 2
>> Panthera-leo | Panthera | 1
>> Panthera-tirgis | Panthera | 1
>>
>
> I am not entirely following the above. Could you post the actual table
> definitions?
>
>
>
>> Is that table structure sufficient for PostgreSQL to calculate
>> grand-children, etc., or will I have to modify it? I think the key words
>> are "hierarchical query" and/or "nested set." There's a popular tutorial
>> (though I can't find it at the moment) that illustrates the procedure,
>> which involves creating TWO numerical fields - a process that I think
>> would be overwhelming when working with over 50,000 taxonomic names.
>>
>> So that's my question; can I do all this recursive stuff in Postgre with
>> the table structure posted above, or will I still have to add a second
>> numerical column (or otherwise my table)?
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org