Treeview

Hello dear Jean and all MVD users, and ... happy new year !!

Haaaaa, recursive queries.... "tout un monde !!!" (en français dans le texte). Sorry in advance, this answer might be a little long

To start with, I do not know how you set up you database. I am going to assume that you have at least two tables : one with the butterflies families and one with the butterflies themselves.

What I set up as an example looks like this :

• the butterflyFamilies table contains :

  • a unique ID

  • a family Name

  • the ID of the parent family

• the butterflies table contains :

  • a unique ID

  • a butterfly Name

  • the ID of the family this butterfly belongs to (the lowest sub-family in fact, not the main branch)

At this stage, and based on your screen-capture, it is not difficult to count, for each family, how many butterflies are attached to it. But if these families are in fact sub-families, how can we count (or sum) how many butterflies there are in each main families ?

Again, in your screen-capture, the Nymphalidés family should count 21, which is the sum of all butterflies belonging to each sub-familes.

For this, you can not use "nodecount" because butterflies or not subnodes of each families, they are in another table.

You will have to use a RECURSIVE query.

In a simple query, you select N fields by their names in a table. In a RECURSIVE query, one (or more) of the fields you will retrieve is not a named field, but a calculated field counting ID from a query in the main query. The "magic" being that the query interrogates itself until no more results are found !! For this, we will create a virtual table stored in memory (I called it IN_MEMORY_TABLE but you can call it whatever you want, the only reserved word being WITH RECURSIVE). This works with Sqlite and is called CTE (common table expressions). You can even solve suddokus with this type of query.

Good link for explanation : https://sqlite.org/lang_with.html

God, I hope I am being clear With an example, things will be simpler.

NOTE : I am not a specialist in butterflies, I merely googled for names (and even invented some) just for the table to look pretty

Based on my tables model (you might have to adapt it to you scheme) the query looks like this :

SELECT DISTINCT
    familyID AS FID,
    familyParentID AS FPID,
    familyName AS FN,
    (
    SELECT
        COUNT( butterflyID ) 
    FROM
        butterflies 
    WHERE
        butterflyFamilyID IN (
            WITH RECURSIVE IN_MEMORY_TABLE ( familyID ) AS ( VALUES ( butterflyFamilies.familyID ) UNION ALL SELECT butterflyFamilies.familyID FROM butterflyFamilies JOIN IN_MEMORY_TABLE ON butterflyFamilies.familyParentID = IN_MEMORY_TABLE.familyID ) SELECT
            familyID AS TREE 
        FROM
            IN_MEMORY_TABLE 
        ) 
    ) AS NB_BUTTERFLIES 
FROM
    butterflyFamilies 
ORDER BY
    familyID,
    familyName

You see that, the beginning of the query is pretty standard, we are selecting 3 fields from the butterfyFamilies table. The fourth one, the one called NB_BUTTERFLIES, is the one counting butterflies attached to each families, and recursively counting them for each main family.

The result of this query is :

As you can see, the Nymphalidés family now counts 21 members, decomposed into 3 sub-families having each 7, 2 and 12 members.

i hope this helped you a little. Do not hesitate to come back to me if you table structure is different or if anythings is unclear. you will find a zip archive attached with the sqlite3 database I used and the query file if you want to test with my data.

Again happy new year.

Cheers

Mathias

Hello Mathmatou

I'm glad to see you back on the forum.
In my turn, I wish you my best wishes (especially health) for 2021.
Thank you for your answer. I'm studying this and keeping you posted.
Thanks again.

JB

Hello Derek

Thank for your answer.
Good idea the calculated field.
Especially since I already use one to totalize each sub-family.
When I use yours, I get an error: List Index out of bound (49) (I entered 50 butterflies). There is already a problem in my code.
So, this error prevents me from performing an (Up and Down) to alphabetically reorganize the families (root parents) and the subfamilies (children).
Can I send you my project because I am going around in circles and it annoys me because it is certainly not much, I put my eyes out on the code.
If you agree, please can you give me a personal address or I can send it to you? Or else by dropbox.
Otherwise, I will end up finding the bug but first I would have eaten my keyboard.
Thanks in advance.
JB