Implementing TOP / LIMIT

[Thomas Messier]

I saw a number of posts asking about TOP / LIMIT functionality, and it
seems there's no plan to implement that in Transfer because of the
vastly different implementations accross DBs. Would it be possible to
at least provide basic functionality to limit a query to n records by
using cfquery's maxrows attribute? I realize that it's easy to write a
manual SQL query to do this, but it would be nice to not lose the
abstraction, caching, etc. that Transfer provides. I know I would
really benefit from this, I frequently have to retrieve the latest n
posts/items from a list of items. So while the approach above wouldn't
help for paging, it could still come in handy. Any ideas/comments?

[Derek P.]

So, this will never be added to the internal project..Why? There are too many issues supporting all the databases that Transfer does, and if Mark were to internally do a QoQ himself, you'd be on here asking us why your 20 row query is taking forever because it will be downloading 20k rows in the background pre-filter.

you're best bet is to just do a query of query that has a maxrows attribute.

you can alternatively do a cfoutput tag with a start and stop.

Derek P.

de...@derekperez.com

[Thomas Messier]

I wasn't thinking query of query. Like I said, what I had in mind was using the maxrows attribute in the cfquery tag to limit the number of records for a query. Now that doesn't cover other more complex cases, but it would already be something handy and it doesn't seem to require a different implementation for each DB type. If that's not the case, or if for some other technical reason using maxrows within the core Transfer code isn't feasible, then so be it. I was just throwing it out there in case it hadn't been done because maxrows would be limited as opposed to the power of the LIMIT clause in MySQL.

 

-TM

---

From: transf...@googlegroups.com [mailto:transf...@googlegroups.com] On Behalf Of Derek P.
Sent: Tuesday, September 11, 2007 7:28 PM
To: transf...@googlegroups.com
Subject: [transfer-dev] Re: Implementing TOP / LIMIT

[Sean Corfield]

On 9/11/07, Thomas Messier <thomas....@gmail.com> wrote:
> I saw a number of posts asking about TOP / LIMIT functionality, and it
> seems there's no plan to implement that in Transfer because of the
> vastly different implementations accross DBs.

Well, I'm not entirely convinced that it's an intractable problem...

In reality, although the syntax differs for each DB, there are only a
fixed number of syntaxes and Transfer *does* know which DB you're
using (mssql, mysql, postgresql, oracle) so there are only four
syntaxes max.

If someone can list the four syntaxes for those DB types, I'm willing
to have a go at implementing it inside the QueryBuilder and working
with Mark on integrating it.

I *think* it's

MS SQL:

SELECT TOP n rest_of_query

MySQL / PostgreSQL:

SELECT rest_of_query LIMIT n

and for Oracle:

SELECT columns_etc WHERE ROWNUM < n AND rest_of_where_clause

(Oracle being the trickiest of course!)
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/

"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood

[Thomas Messier]

Hmmmmm... I did do some research before posting and I was under the
impression that Oracle was more of a pain than that. It seemed like for some
reason using the ROWNUM < approach didn't work properly when you had an
ORDER BY clause. As a result, people are forced to use subqueries. I could
be wrong though. Even then, I guess anything is doable somehow, just takes
more time and effort and I understand that Mark already has plenty on his
plate. I'll try to do some more research on it.

-TM

-----Original Message-----
From: transf...@googlegroups.com [mailto:transf...@googlegroups.com]
On Behalf Of Sean Corfield
Sent: Tuesday, September 11, 2007 8:52 PM
To: transf...@googlegroups.com
Subject: [transfer-dev] Re: Implementing TOP / LIMIT

[Mark Mandel]

Oracle is the pain -

If I remember correct you need to subselect the query, because if you
order by, the rownum is out of order with what is originally in the
query.

LIke so: http://www.anticlue.net/archives/000391.htm

I don't know if this was changed in 9i+ (since Transfer only supports 9i+)

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Dan Wilson]

I believe SQL server works the same way using the TOP keyword.


DW

Guillaume Apollinaire quotes

[Mark Mandel]

Btw, Sean (or anyone else) - you are more than able to implement this
stuff and fire it across

Atm, I'm knee deep in CF8 performance improvements (and some for CF7
too), while I'm also waiting for some feedback on the composite key
CRUD stuff (hint hint ;o) )

Mark

[Harry Klein]

Sean,

this is a sample query with cases for mssql, oracle, postgresql, mysql
and db2.

<cfquery name="qData"
datasource="#variables.stContens.sDatasource#">
SELECT
<!--- limit rows, rdbms specific --->
<cfif variables.stContens.sDbSys is
"mssql" AND arguments.iMaxrows GT 0>
TOP #arguments.iMaxrows#
</cfif>
co_apps.app_ID,co_apps.appname,...

FROM co_apps
WHERE
<!--- limit rows, rdbms specific --->
<cfif variables.stContens.sDbSys is
"oracle" AND arguments.iMaxrows GT 0>
AND ROWNUM <=
#arguments.iMaxrows#
</cfif>

<!--- sort order --->
<cfif len(arguments.sortOrder)>
ORDER BY #arguments.sortOrder#
</cfif>

<!--- limit rows, rdbms specific --->
<cfif arguments.iMaxrows GT 0>
<cfif ListFind("pgsql,mysql",
variables.stContens.sDbSys)>
LIMIT #arguments.iMaxrows#
<cfelseif variables.stContens.sDbSys IS
"db2">
FETCH FIRST #arguments.iMaxrows#
ROWS ONLY
</cfif>
</cfif>
</cfquery>

--

Harry Klein | Konzeption und Entwicklung
CONTENS Software GmbH
Oettingenstr. 25 | 80538 München
Fon: +49 (0)89 5199 69-0 | Fax: +49 (0)89 5199 69-78
mailto:kl...@contens.de | http://www.contens.de

****************************
Sitz der Gesellschaft: München
Geschäftsführer: Jens Hoppe
Amtsgericht München, HRB 128 273

> -----Ursprüngliche Nachricht-----
> Von: transf...@googlegroups.com
> [mailto:transf...@googlegroups.com] Im Auftrag von Sean Corfield
> Gesendet: Mittwoch, 12. September 2007 02:52
> An: transf...@googlegroups.com
> Betreff: [transfer-dev] Re: Implementing TOP / LIMIT

[Thomas Messier]

Harry,

It seems that in Oracle ROWNUM is processed before an ORDER BY, which
doesn't yield what we'd expect if you have an ORDER BY (unless it's changed
in recent versions). Here's an article that illustrates that:
http://www.oracle-base.com/articles/misc/TopNQueries.php This article also
illustrates how to make things work properly in Oracle, which would be as
follows:

SELECT id
FROM (SELECT id
FROM rownum_order_test
ORDER BY id DESC)
WHERE rownum <= 5

-TM

-----Original Message-----
From: transf...@googlegroups.com [mailto:transf...@googlegroups.com]

[Harry Klein]

> It seems that in Oracle ROWNUM is processed before an ORDER
> BY, which doesn't yield what we'd expect if you have an ORDER
> BY (unless it's changed in recent versions). Here's an
> article that illustrates that:
> http://www.oracle-base.com/articles/misc/TopNQueries.php This
> article also illustrates how to make things work properly in
> Oracle, which would be as
> follows:
>
> SELECT id
> FROM (SELECT id
> FROM rownum_order_test
> ORDER BY id DESC)
> WHERE rownum <= 5
>
> -TM

Thanks for this hint Thomas. I tested with Oracle 10, this behaviour was
not changed.

-Harry