How to remove a query from a loop....

Derokorian

So I have a query that gets me all the transactions for a register. Then while looping over that data to build a report, we run another query to get the beginning balance. The query looks like this:

SELECT balance_after FROM register_transactions WHERE register_id = ? and trans_date <= ? ORDER BY trans_id DESC LIMIT 0,1

Which gets the balance after the last transaction ran before the beginning date of the report, aka giving me the starting balance for the report. Now since it relies on order by and limit like this, is there anyway (its completely escaping me atm) to change this to a regster_id IN (list of registers we're running the report for) so its not a n+1 problem?

NogDog

If I'm correctly understanding the question (not a given), I think one approach would be a sub-query in the SELECT clause to get the "before" value, something along the lines of...

select
  rt1.register_id
  (
    SELECT balance_after 
    FROM register_transactions
    WHERE register_id = rt1.register_id and trans_date <= ?
    ORDER BY trans_id DESC LIMIT 0,1
  ) AS starting_balance,
  rt1.balance_after
FROM register_transactions AS rt1
WHERE rt1.trans_date > ?
-- order by or whatever else here?

Make any sense?

Derokorian

That does make sense, the only problem is we're fetching far more than one row per register, and using a dependent sub-query means it gets run for every ROW returned (even if the result is the same). I'm not sure this would be any more performant (plus I hate sub-queries with a passion) but its worth giving a shot.

If it helps here is the basics of the query we're looping over, and then we only run the query for beginning balance the first time we see a register:

SELECT
    register_id,
    name,
    transaction_type,
    SUM(amount) as amount
FROM 
    register_transactions
JOIN
    register USING (register_id)
GROUP BY
    transaction_type, 
    register_id
ORDER BY created

Unfortunately I can't use aggregate functions like MIN?MAX because we can't guarantee that the beginning balance is the minimum balance after, for example if the first transaction for the day is a refund as opposed to a sale.

NogDog

argh...this is the sort of question I like to puzzle over, but right now DB stuff at work has my brain clogged and isn't letting your stuff in 🙁

sneakyimp

Is there some way to join the table on itself? You can't do a LIMIT in a join but perhaps there is some other constraint like t1.trans_id = (t2.trans_id-1)?

Derokorian

sneakyimp;11052089 wrote:
Is there some way to join the table on itself? You can't do a LIMIT in a join but perhaps there is some other constraint like t1.trans_id = (t2.trans_id-1)?

Well, there are multiple registers and even locations which are possible - therefore there's no guarantee that trans_id - 1 would be for the same register. I tried using a JOIN reg_trans t2 ON t2.trans_id = MIN(t1.trans_id) but you can't use aggregate functions in an ON clause -\

NogDog;11052073 wrote:
argh...this is the sort of question I like to puzzle over, but right now DB stuff at work has my brain clogged and isn't letting your stuff in 🙁

Yeah, no worries man - I just really hate this being in a loop. This is the type of stuff people come to ME for and I just can't figure it out =\

sneakyimp

Derokorian;11052093 wrote:
Yeah, no worries man - I just really hate this being in a loop. This is the type of stuff people come to ME for and I just can't figure it out =[/QUOTE]
Is there some way to iterate through the results from your first query and generate a PHP data structure (like an array of trans_id values) such that you can run just ONE query to fetch a second data set and then stitch the two together in PHP? Does that make any sense?

Derokorian

sneakyimp;11052115 wrote:
Is there some way to iterate through the results from your first query and generate a PHP data structure (like an array of trans_id values) such that you can run just ONE query to fetch a second data set and then stitch the two together in PHP? Does that make any sense?

It does, and that's how I've modified much of this report. I've gotten rid of every sub-query and every query in a loop (total 9 sub-queries and 11 loop queries) and this is the only one left. I have TRIED my best to figure it out, but I think we're just going to leave this one for now =\ There's a lot of bad data structure from the previous regime (who have all been asked to leave) so... yay to that 😛