4

I'm stumped. I need to access the next nth row in a query loop to show version differences between posts.

I'm using <cfquery> to output the revisions by group, and this is my expected output:

Rev4
    diff(rev4.title, original.title)
    diff(rev4.brief, rev2.brief)
Rev3
    diff(rev3.body, rev2.body)
Rev2
    diff(rev2.brief, original.brief)
    diff(rev2.body, original.body)
Original
    query.title
    query.brief
    query.body

I initially thought to use Java methods to get the next query row. This does not work:

  • Rev4 needs to show the difference between its own brief row, and the last revision made to the brief row; which, in this case, occurred in Rev2; so it needs to jump one row.
  • In order to show the diff for its title row, Rev4 needs to jump to the original post since the first change to the title row occurred in Rev4 itself.

Some things to consider:

  1. The revisions schema is one row for each edited post column; so if you load a post and edit its title and body, two records will get created in the revisions schema; one for the title and one for the body, under the same revisionGUID.
  2. The query is grouped by revisionGUID.
  3. It's ordered by revision date, newest to oldest; then by revision type (title, brief, body).

I tagged this with Java because ColdFusion allows us to use Java methods on queries objects, but it's not documented so merely knowing of its existence does not help me.

Anyone can show me a [better] way to do this?

The code structure I thought of:

<cfoutput query="revisions" group="revisionGUID">
    #revision.revisionGUID#
    <cfoutput>
        // conditional logic to get diff();
    <cfoutput>
</cfoutput>
Community
  • 1
  • 1
Mohamad
  • 34,731
  • 32
  • 140
  • 219
  • Inside a `` or `` over a query is the variable `currentrow`, which tells you what iteration in the record set you're in. Also ColdFusion query columns can be accessed by index, so you can write `revision.revisionGuid[3]` to get the third row (it's one, not zero based) or `revision.revisionGuid[currentrow+1]` where the value is an expression. You'll need to write some conditionals to avoid referencing something out of bounds. – orangepips Aug 04 '11 at 20:26
  • orangepips, I'm aware of `currentRow` but it can't tell me if the next the next rev to diff is in row1, 2, or 3, etc... interesting on the currentRow+1... if the output was always known, it would be much easier... but I have a feeling it's not going to help here because of the unknown factors: which row do we need to jump to get the value. – Mohamad Aug 04 '11 at 20:36

2 Answers2

1

Sorry that I don't know Coldfusion, but it sounds like (Java) scrollable resultSet might be useful.

From the oracle info page:

5.1 Scrolling
A result set created by executing a statement may support the ability to move backward (last-to-first) through its contents, as well as forward (first-to-last). Result sets that support this capability are called scrollable result sets. Result sets that are scrollable also support relative and absolute positioning. Absolute positioning is the ability to move directly to a row by specifying its absolute position in the result set, while relative positioning gives the ability to move to a row by specifying a position that is relative to the current row. (link: result set enhancements)

And the ResultSet api briefly mentions it at the top:

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

http://download.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSet.html

Hope this helps you find what you are looking for =)

Community
  • 1
  • 1
sova
  • 5,468
  • 10
  • 40
  • 48
1

How about if you let the database help you and use the following queries to find out the previous values of your fields:

SELECT TITLE AS PREVIOUSTITLE
FROM REVISIONS
WHERE ID < #query.id# AND TITLE <> '#query.title#'
ORDER BY ID DESC

SELECT BRIEF AS PREVIOUSBRIEF
FROM REVISIONS
WHERE ID < #query.id# AND BRIEF <> '#query.brief#'
ORDER BY ID DESC

SELECT BODY AS PREVIOUSBODY
FROM REVISIONS
WHERE ID < #query.id# AND BODY <> '#query.body#'
ORDER BY ID DESC

If the recordcount is 0, this means that the field has never changed.

Marcelo
  • 11,218
  • 1
  • 37
  • 51