How do I discard a row from a ColdFusion query?

Question

Given a query (pseudo-code):

<cfquery name="myquery">SELECT * FROM stuff</cfquery>

How do I get rid of the first record? In this case, altering the SQL is not an option. I have tried: myquery.RemoveRows(0,1); but received an error:

No matching Method/Function for Query.REMOVEROWS(numeric, numeric) found

I'm on Railo 3 BTW

Hon

The RemoveRow function is a neat solution. But why do you want to remove the row? Depending on what you're doing with your query, there may be other ways to achieve the same result without removing query rows. – Wicketkeeper

I do understand that it would be better to do it in the query, but the framework i'm using in this case (CFWheels) doesn't take an offest argument on its findAll() method. I could dig in and get my hands a little more dirty, but this yields a nice one-line solution – Hon

Answer 1

Lo and behold:

myquery.RemoveRow(1);

Does exactly what I wanted. Leave it to Railo to do things a little differently!

Hon

Nice. I didn't think to check the Railo docs to see if there was a Railo-specific implementation. – Falcone

Answer 2

Can't think of a way offhand to remove a row from the original object. Two things I can think of are:

1. do a query of queries. That assumes you'd be able to identify the records you don't want and specify them in the WHERE.

2. construct a new query with queryNew(). Loop over the original query doing a querySetCell() into the new query for the records that you want. This functionality could be incorporated into a UDF pretty easily. In fact, stating that made me think to check cflib.org. See #3

3. Check cflib.org :) See http://www.cflib.org/udf/QueryDeleteRows

Falcone

Well instead of being sad that there's no built in method, be happy that in this case, there are (at least) 2 ways to get around it :) – Falcone

Answer 3

"RemoveRows" is actually a call to the underlying Java method, so you have to cast those numbers. like so:

myquery.RemoveRows(
    JavaCast( "int", 0 ) // starting row, zero-based
    ,JavaCast( "int", 1 ) // number to delete, returns error if too many
)

So probably the method is "int,int", and if you don't cast it, it looks like "numeric,numeric". One might argue that this is undocumented, but it's so succinct that you could (as I did) wrap it in a function, so that if it changes you just need to replace the contents of the function with an alternative workaround.

Pemberton

Answer 4

Railo has added removeRows, see here. My ACF code that uses this method now runs on Railo too, no changes.

With this, the Railo implementation now matches ACF. (Note also that the original Railo implementation was 0 based, while the new one and the ACF version are both 1 based.)

Asphyxiate

Answer 5

The way I would normally do it is with a query of queries such as the following:

SELECT * FROM myquery
LIMIT {1,10000}

That should work in Railo. What it does is offset the query by one and pulls 10000 records.

You could also do this:

SELECT * FROM myquery
WHERE {primarykey} <> {value}

Where it selects all records except for the primary key value that you pass in.

The awesome thing about ColdFusion is that there are tons of ways to do exactly what you are looking for.

Moyer

Answer 6

You could just skip the row when you process the results:

<cfoutput query="myquery">
  <cfif myquery.currentrow GT 1>
    <!---Do the work here. --->
  </cfif>
</cfoutput>

Indecisive

or just set startrow=2 to avoid having to do an IF statement on every iteration of the query. – Wicketkeeper

Well it depends on how much you're outputting. If it's zillions of rows, no, you wouldn't want to run cfif on each iteration. But if you're displaying a small amount, skipping that 1st row may be easier and just as quick as modifying the query object to drop row 1. – Indecisive