Query in a gnucash table

From: Joseph Hesse <joeh_at_gmail.com>
Date: Thu, 12 Oct 2017 13:59:49 -0500
Message-ID: <yOqdnUU_1o84JkLEnZ2dnUU7-b3NnZ2d_at_giganews.com>

[Quoted] [Quoted] This question refers to the following table from the gnucash program. The 3 fields of interest are marked with arrows.

MariaDB [gnucash]> describe accounts;

+----------------+---------------+------+-----+---------+-------+


| Field          | Type          | Null | Key | Default | Extra |


+----------------+---------------+------+-----+---------+-------+


| guid           | varchar(32)   | NO   | PRI | NULL    |       | <==

| name           | varchar(2048) | NO   |     | NULL    |       | <==

| account_type   | varchar(2048) | NO   |     | NULL    |       |

| commodity_guid | varchar(32)   | YES  |     | NULL    |       |

| commodity_scu  | int(11)       | NO   |     | NULL    |       |

| non_std_scu    | int(11)       | NO   |     | NULL    |       |

| parent_guid    | varchar(32)   | YES  |     | NULL    |       | <==

| code           | varchar(2048) | YES  |     | NULL    |       |

| description    | varchar(2048) | YES  |     | NULL    |       |

| hidden         | int(11)       | YES  |     | NULL    |       |


| placeholder    | int(11)       | YES  |     | NULL    |       |


+----------------+---------------+------+-----+---------+-------+

The table describes the accounts I have. The accounts are hierarchical but the table displays only one item in the hierarchy. For example, I have an account:

expenses:auto:ford:gas

[Quoted] Each of the four parts above are in separate rows. If a row has name "gas" then in order to find "ford" you have to look at the parent_guid in that row, locate the row where guid matches parent_guid and look at that name, which should be "ford". This process is repeated until all the parent items of "gas" are found.

My questions is the following. I would like to add an additional column to the table called full_name which gives all of the items in the hierarchy above the value in the name column. Referring to the above example, if the row contains "gas", the full_name column should be "expenses:auto:ford:gas".

Is there a query that can accomplish what I want? I'm sure I could use some scripting language like PHP to accomplish what I want.

Thank you,
Joe Received on Thu Oct 12 2017 - 20:59:49 CEST