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

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. — duncan, Apr 23 '11 at 11:06
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 — Jordan Sitkin, Apr 25 '11 at 03:00

score 11 · Accepted Answer · answered Apr 21 '11 at 23:58

11

Lo and behold:

myquery.RemoveRow(1);

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

answered Apr 21 '11 at 23:58

Jordan Sitkin

2,303
3
26
35

Nice. I didn't think to check the Railo docs to see if there was a Railo-specific implementation. – charliegriefer Apr 22 '11 at 02:17

score 4 · Answer 2 · edited Mar 21 '12 at 15:03

4

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

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.
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
Check cflib.org :) See http://www.cflib.org/udf/QueryDeleteRows

edited Mar 21 '12 at 15:03

ale

6,369
7
55
65

answered Apr 21 '11 at 23:42

charliegriefer

3,342
1
18
20

1

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 :) – charliegriefer Apr 21 '11 at 23:51

score 2 · Answer 3 · answered Aug 25 '11 at 19:20

"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.

score 2 · Answer 4 · edited Mar 20 '12 at 13:22

2

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.)

edited Mar 20 '12 at 13:22

ale

6,369
7
55
65

answered Mar 19 '12 at 03:17

enigment

3,316
7
30
35

score 0 · Answer 5 · answered Apr 22 '11 at 02:11

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.

score 0 · Answer 6 · answered Apr 27 '11 at 04:46

0

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>

answered Apr 27 '11 at 04:46

Dan Sorensen

11,403
19
67
100

1

or just set startrow=2 to avoid having to do an IF statement on every iteration of the query. – duncan May 03 '11 at 10:08
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. – Dan Sorensen May 03 '11 at 16:41

How do I discard a row from a ColdFusion query?

6 Answers6