Re: Query in a gnucash table

On 10/12/2017 4:28 PM, Joseph Hesse wrote:
> On 10/12/2017 02:11 PM, Jerry Stuckle wrote:

>> On 10/12/2017 2:59 PM, Joseph Hesse wrote:
>>> 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
>>>
>>> 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".
>>>
[Quoted] [Quoted] >>> 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
>>
>> Probably, but your question is not clear.  What would the sample data 
>> for such a table look like?
>>

OK, I'm not very impressed with the table design (the same column refers to different types of objects). But we have to work with what we have.

This is a good use for recursive SQL. What you do NOT want to do is add a fourth column with the information - maintenance of such a column would be terrible.

You can use recursive SQL with a common table expression (CTE). It is not as straightforward as non-recursive SQL, and I'll admit I'm not an expert at it. However, maybe something like (warning - completely untested!):

WITH RECURSIVE hierarchy AS (

    SELECT name,':'
    FROM gnucash
    WHERE <your selection criteria here>     UNION
    SELECT b.name,':'
    FROM gnucash b, hierarchy a
    WHERE b.guid = a.parent_guid
}
select * from hierarchy;

You'll need to modify as required, and once you get it working you could place it in a view.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================