Query for splits

[Derek Atkins]

Hi,

Quoting Phil Longstaff <plongstaff at rogers.com>:

> I just want to verify what needs to be done.
>
> When I open an account, the backend gets a QofQuery which translates
> into SQL as:
>
> 	SELECT * FROM splits WHERE book.guid = "..." AND account.guid = "..."
>
> I assume that what is really needed is:
>
> 	SELECT * FROM transactions
> 		WHERE guid IN (SELECT unique tx_guid FROM splits
> 						WHERE account_guid = "...")

Transactions/Splits are.... interesting.  In the end, yes, what you
want to do is pull in splits all transactions that match the query.
But you clearly need to perform multiple SQL Queries for this QofQuery.
In particular you clearly need to do three things:

1) Find all Splits that match the QofQuery
2) Find all Transactions that contain the Split response
3) Find all Splits from all those Transactions.

How you do these three steps is sort of up to you.  I suppose you could
keep Split/Transactions "special" in the code....

> This assumes that when a transaction is loaded, all of its splits are
> loaded too.  When the transactions are created, they are put into the
> engine.  The splits are created as children of the transactions.  The
> set of splits matching the initial query are put into the query's result
> set.

Yes, that's what needs to happen...

> I haven't looked at the sorting in the initial query, so I don't know
> how it would translate to a transaction SELECT

Which is why I don't think you could do this translation; I think you
still need to make three distinct SQL Queries.

> I guess I had better check that a transaction hasn't already been loaded
> before I reload it.

Not only that -- you need to check whether the transaction content in the
DB is "newer" than the cache in the engine.  E.g. in a multi-user situation
one user may have modified the contents of a transaction, so when another
user performs a 'refresh' you DO have to load the new contents into the
engine.

So.. "loaded" is not a sufficient check for "should I refresh the engine
cache"...  I'll note that we might need to modify QOF to also maintain
an internal "last modified" tag in order to help us here.

> Phil

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available